Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    Chris Ochs
    @gamemachine
    Queries by primary key are mostly via the cache, it's a mix of other non primary key queries that would go straight through to postgres
    nilpatel13
    @nilpatel13
    To enable Logical replication, postgres restart is must? Any workaround? because i don't want to restart it.
    Shimmy
    @weitzhandler
    Is PostgreSQL better than SQL Server, to be used with EF Core?
    two10
    @two10
    Hello guys in my case there are ~ 50 columns in the table (varchar,date, boolean,json) and user can create search query for any combination of the column, what will be the best approach to design such . Any reference docs or research papers are welcome
    Kontributer
    @Kontributer
    @two10 I am having trouble understanding your problem and what you are after. Also, more than likely you don’t want to use varchar
    two10
    @two10
    Hello @Kontributer i have a table which contains 50 columns some of them are text , some are date , some are numbers. Now in our use case one could query the table with condition (containing any combination of columns like col1='' and col2='' and col3 != '' or col4 ... order by DATE_CREATED or DATE _MODIFIED). I tried creating indexes on all the columns separately but that dosen't worked well. I am looking for ideas on how to create indexes to support such searches as table contains 10 million records and when system goes for seqscan it takes too much time
    Kontributer
    @Kontributer
    Having too many indexes could work against you, ultimately only you have access to your data to make that decision. In some cases, you may need replicated data logically arranged, or even distributed across different hardware. Too many possibilities to give any specific advice
    two10
    @two10
    @Kontributer if i am using only a single machine , what is the best possible way to index those items
    Kontributer
    @Kontributer
    @two10 a single machine doesn’t mention how many drives/procs or how much mem. But still it’s dependent on your tables and use cases.
    Alex
    @alexc101

    Hello, I have a general API design question if anyone can help.

    I have separated User and User_Profile into two different tables in my SQL db. I would like to know if it is better to implement a database trigger on the User table so it automatically creates a profile on insert, or if it's better to make two insert calls from node

    Kontributer
    @Kontributer
    A trigger is almost always more efficient
    ksu-talenta-ntt
    @ksu-talenta-ntt
    Hi guys... Hope all of you have a nice day. I'm trying to restore database with psql. But psql always complains when it found the foreign key constraint. It just halt there. After looking into generated dump, looks like the child table get created before the parent table. Is there a way to solve this?
    Ben Johnson
    @cbj4074
    @ksu-talenta-ntt You can use the --disable-triggers option for pg_restore.
    Here's my full "recipe" that I use, e.g., in a Bash script (the sample database here is called www, and the data is being restored from the file db.custom:
    SQL='SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();'
    sudo -H -u postgres /bin/sh -c "psql -d www -c '$SQL'"
    
    sudo -H -u postgres /bin/sh -c 'dropdb --if-exists www'
    sudo -H -u postgres /bin/sh -c 'createdb -O vagrant www'
    
    sudo -H -u postgres /bin/sh -c "psql -d www -c 'CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;'"
    
    sudo -H -u postgres /bin/sh -c 'pg_restore --dbname=www --role=vagrant --no-owner --no-acl --no-security-labels --no-tablespaces --schema-only /home/vagrant/Code/www/database/db.custom'
    
    sudo -H -u postgres /bin/sh -c 'pg_restore --dbname=www --role=vagrant --no-owner --no-acl --no-data-for-failed-tables --no-security-labels --no-tablespaces --data-only --disable-triggers --superuser=postgres /home/vagrant/Code/www/database/db.custom'
    
    if [ $? -ne 0 ]
    then
        echo "Loading project database failed (ensure that there are no active connections to the database!), so not continuing"
        exit 1
    fi
    Your needs may differ, but I figured it would at least be helpful to show you how I do it. Completely restoring a DB in PostgreSQL is far less trivial than it is in, say, MySQL.
    That first bit is necessary because you can't DROP certain resources if any connection to them exists.
    ajay yadav
    @ajay7868
    Hi any one help how to check postgres version
    i am ubuntu user
    Kontributer
    @Kontributer
    @ajay7868 can you access Postgres via psql?
    ajay yadav
    @ajay7868
    no i am unable to find
    how to install
    Henri Cook
    @henricook

    Hi all, i use serializable transaction isolation and i've recreated a weird situation. Using a one column table 'foo' with column 'id' (integer) and no PK or constraints. Two rows, id = 44 and id = 33.

    T1: SELECT * FROM foo WHERE id =44;
    T2: SELECT * FROM foo WHERE id = 33;
    T2: INSERT INTO foo (id) VALUES (1);
    T2: COMMIT;
    
    T1: INSERT INTO foo (id) VALUES (2);

    ^^^^ T1 gets a serialisation error

    Does anyone know why that might be? It seems to me that a SELECT in one transaction shouldn't interfere with an insert in another
    Henri Cook
    @henricook
    Full test script:
    DROP TABLE IF EXISTS foo;
    CREATE TABLE foo(id int);
    INSERT INTO foo (id) VALUES (44);
    INSERT INTO foo (id) VALUES (33);
    
    -- T1:
        BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        SELECT id FROM foo WHERE id = 44;
    
    -- T2:
        BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        SELECT id FROM foo WHERE id = 33;
        INSERT INTO foo (id) VALUES (1);
        COMMIT;
    
    -- T1:
        INSERT INTO foo (id) VALUES (2);
        ROLLBACK
    Henri Cook
    @henricook
    I'm on 10.5 btw
    Henri Cook
    @henricook
    even with a CREATE TABLE foo(id int PRIMARY KEY); this also fails and is confounding us! Anyone with some insight would be greatly appreciated
    Henri Cook
    @henricook
    The pgsql-bugs mailing list responded very quickly, if anyone wanted to see how this turned out
    qingguee
    @qingguee
    Hello, does anyone know how to configure postgresql to limited the retry when the authentication failed? For example, someone try to connect the DB but failed 10 times with a password, then DB will ignore the conneciton for that user for 5 minute?
    chsi13
    @chsi13
    Hi! What is the faster way , atomic update with condition(where clause) or in 2 queries: select exists(where clause) + update if select exists = true. In major case select exist will return false....
    dimtabu
    @taburetkin

    Guys, if some one can point me to the docs - it will be very nice.
    i am looking info for parametrized insert with composite types.
    i want to know if this is possible at all and if yes how to do that and where to read?

      CREATE TYPE someType AS
      (
        sometext character varying(128),
        somenum smallint,
      );
    
      CREATE TABLE "sometable "
      (
          id uuid NOT NULL default uuid_generate_v4() PRIMARY KEY,
          someField someType
      )
    
      insert into sometable (someCompositeField) values ($1)

    can't determine what parameter should look like for this composite type field

    dimtabu
    @taburetkin
    nvm. found
    Ajay Pratap
    @apple_ajay_twitter
    Hello, what is the correct way to provide PostgreSQL password in hibernate.properties(encrypted or plain). If encrypted, who is responsible for encryption and decryption Please post any doc which is helpful. Thanks
    Jesse Lieberg
    @GammaGames
    Did the storage format for user passwords change somewhere from 9.5 to 11? I am using pg_dumpall --roles-only to copy users from 9.5 to 11 and users can't log in with their old password, and if they change their password on both databases to the same thing the password hash is different
    Jesse Lieberg
    @GammaGames
    Looks like psql 10 had a major enhancement with "Stronger password authentication based on SCRAM-SHA-256," guess that must make some old passwords not work
    Phil Cockfield
    @philcockfield
    If I’m running RegEx WHERE clauses over a column of type text, what is the optimal way to index that column for the regular-expression evaluation to be most performant?
    Pradyumna
    @Prady5543_gitlab
    Hi Team can I get wal2json.so file for postgresql-10.9
    ?
    Jeremy Lewallen
    @deacons2016
    Hi - when you are listening to a channel, if you execute notify channel should anything appear in the console output??
    I am having trouble determining if my listener / notification is working
    gowthamakanthan
    @gowthamakanthan
    I have a basic question related to the pg_basebackup. Is this the right place to discuss ?
    JavierGIS
    @JavierGIS
    Good Morning.
    Can someone give me some guidance on how to make this work:
                dijkstra AS (
                    SELECT *
                    FROM wrk3_dijkstravia(
                        '%1$I',
    
                       ARRAY[(SELECT id FROM vertices
                            ORDER BY the_geom <-> ST_SetSRID(ST_Point(%2$s, %3$s), 4258) LIMIT 1)]
                ))
    Repeat as many times as "x" and "y" data in the Array
    this is my function complete:
    CREATE OR REPLACE FUNCTION wrk1_fromAtoB(
        IN edges_subset regclass,
        IN x ANYARRAY, IN y ANYARRAY,
        OUT seq INTEGER,
        OUT gid BIGINT,
    OUT name TEXT,
    OUT distancia_m NUMERIC,
    OUT distancia_total NUMERIC, 
    OUT cost NUMERIC, 
    OUT agg_cost NUMERIC,
    OUT azimuth FLOAT,
    OUT route_agg_cost NUMERIC,
    OUT route_readable TEXT, 
    OUT route_geom geometry )
    
    RETURNS SETOF record AS
    $BODY$
    DECLARE
        final_query TEXT;
    BEGIN
        final_query :=
            FORMAT( $$
                WITH
                vertices AS (
                    SELECT * FROM ways_vertices_pgr
                    WHERE id IN (
                        SELECT source FROM %1$I
                        UNION
                        SELECT target FROM %1$I)
                ),
                dijkstra AS (
                    SELECT *
                    FROM wrk3_dijkstravia(
                        '%1$I',
    
                       ARRAY[(SELECT id FROM vertices
                            ORDER BY the_geom <-> ST_SetSRID(ST_Point(%2$s, %3$s), 4258) LIMIT 1)]
                ))
                SELECT
                    seq,
                    gid,
                    dijkstra.name,
                    TRUNC (ways.length_m::numeric) AS distancia_m,
                    TRUNC (sum(length_m::numeric) OVER (ORDER BY seq), 2) as distancia_total,
                    ROUND (dijkstra.cost::numeric, 2) AS cost,
                    ROUND (dijkstra.agg_cost::numeric, 2) as agg_cost,
                    azimuth,
                    route_agg_cost::NUMERIC,
                    route_readable,
                    route_geom AS geom
                FROM dijkstra LEFT JOIN ways ON dijkstra.edge = ways.gid;$$,
            edges_subset, x,y);
        RAISE notice '%', final_query;
        RETURN QUERY EXECUTE final_query;
    END;
    $BODY$
    LANGUAGE 'plpgsql';
    JavierGIS
    @JavierGIS
    When I execute my function:
    SELECT * FROM wrk1_fromAtoB('vehiculo_tiempo', ARRAY[-7.566651105880737, -7.564001083374024, -7.555857896804809], ARRAY [43.022996134952294, 43.02138816839723, 43.01512061875881])
    I get the error of getting all the coordinates in the ST_Point function:
    ARRAY[(SELECT id FROM vertices
                            ORDER BY the_geom <-> ST_SetSRID(ST_Point({-7.566651105880737,-7.564001083374024,-7.555857896804809}, {43.022996134952294,43.02138816839723,43.01512061875881}), 4258) LIMIT 1)]
    What I need is for this ST_Point function to see the "x" e "y" of my function and pass the coordinates through the St_Point function