Where communities thrive

  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
    Chris Ochs
    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
    To enable Logical replication, postgres restart is must? Any workaround? because i don't want to restart it.
    Is PostgreSQL better than SQL Server, to be used with EF Core?
    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
    @two10 I am having trouble understanding your problem and what you are after. Also, more than likely you don’t want to use varchar
    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
    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
    @Kontributer if i am using only a single machine , what is the best possible way to index those items
    @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.

    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

    A trigger is almost always more efficient
    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
    @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 ]
        echo "Loading project database failed (ensure that there are no active connections to the database!), so not continuing"
        exit 1
    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
    Hi any one help how to check postgres version
    i am ubuntu user
    @ajay7868 can you access Postgres via psql?
    ajay yadav
    no i am unable to find
    how to install
    Henri Cook

    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
    Full test script:
    CREATE TABLE foo(id int);
    INSERT INTO foo (id) VALUES (44);
    INSERT INTO foo (id) VALUES (33);
    -- T1:
        SELECT id FROM foo WHERE id = 44;
    -- T2:
        SELECT id FROM foo WHERE id = 33;
        INSERT INTO foo (id) VALUES (1);
    -- T1:
        INSERT INTO foo (id) VALUES (2);
    Henri Cook
    I'm on 10.5 btw
    Henri Cook
    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
    The pgsql-bugs mailing list responded very quickly, if anyone wanted to see how this turned out
    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?
    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....

    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

    nvm. found
    Ajay Pratap
    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
    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
    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
    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?
    Hi Team can I get wal2json.so file for postgresql-10.9
    Jeremy Lewallen
    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
    I have a basic question related to the pg_basebackup. Is this the right place to discuss ?
    Good Morning.
    Can someone give me some guidance on how to make this work:
                dijkstra AS (
                    SELECT *
                    FROM wrk3_dijkstravia(
                       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:
        IN edges_subset regclass,
        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 )
        final_query TEXT;
        final_query :=
            FORMAT( $$
                vertices AS (
                    SELECT * FROM ways_vertices_pgr
                    WHERE id IN (
                        SELECT source FROM %1$I
                        SELECT target FROM %1$I)
                dijkstra AS (
                    SELECT *
                    FROM wrk3_dijkstravia(
                       ARRAY[(SELECT id FROM vertices
                            ORDER BY the_geom <-> ST_SetSRID(ST_Point(%2$s, %3$s), 4258) LIMIT 1)]
                    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,
                    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;
    LANGUAGE 'plpgsql';
    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