Re: [GENERAL] Nice to have features: Percentage function

Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > *Or, you could just as easily compute inline in SQL:SELECT datname, pg_size_pretty(pg_database_size(datname))as size_pretty

Re: [GENERAL] Service outage: each postgres process use the exact amount of the configured work_mem

esql.org/docs/9.5/static/runtime-config-resource.html > > Bye, > Chris. > > > Sorry, I had a brain fart, shared_buffers should be 25% system memory, or 12GB -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Service outage: each postgres process use the exact amount of the configured work_mem

; you requested). > I tried running some queries against psql server and response times are > good, so I'm moving my attentions to Windows server, which hosts a WCF > service, that is the one that actually server customers. > > Thanks for now > Moreno > > > Il 14/04/2017 20:0

Re: [GENERAL] Service outage: each postgres process use the exact amount of the configured work_mem

On Fri, Apr 14, 2017 at 1:50 PM, Moreno Andreo <moreno.and...@evolu-s.it> wrote: > Sorry, > my mistake (I'm a bit nervous...) > > that's not work_mem, but shared_buffers > > Thanks > > > Il 14/04/2017 19:33, Melvin Davidson ha scritto: > > > > O

Re: [GENERAL] Service outage: each postgres process use the exact amount of the configured work_mem

CONFIG-RESOURCE-MEMORY <https://www.postgresql.org/docs/9.5/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY>"several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary

Re: [GENERAL] Best way to alter a foreign constraint

the referenced table. Refer to system catalogs description in documentaion for more info. SELECT cn.conname, CASE WHEN cn.contype = 'c' THEN 'check' WHEN cn.contype = 'f' THEN 'foreign key' WHEN cn.contype = 'p' THEN 'primary key' WHEN cn.contype = 'u' T

Re: [GENERAL] Constraint + where

On Sun, Mar 19, 2017 at 8:53 PM, Patrick B <patrickbake...@gmail.com> wrote: > > > 2017-03-20 13:27 GMT+13:00 Melvin Davidson <melvin6...@gmail.com>: > >> >> >> On Sun, Mar 19, 2017 at 8:16 PM, Patrick B <patrickbake...@gmail.com> >> wrote

Re: [GENERAL] Constraint + where

On Sun, Mar 19, 2017 at 8:27 PM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Sun, Mar 19, 2017 at 8:16 PM, Patrick B <patrickbake...@gmail.com> > wrote: > >> Hi guys, >> >> I've got a column 'type_note' on a new table that it's being d

Re: [GENERAL] Constraint + where

es > 2. no > 3. maybe > > I wanna create a FK but just when the data on that column is = maybe. > > How can I do that? Thanks! > > Why just "maybe"? Since there can only be three valid answers, why not FK for all three? -- *Melvin Davidson* I reserve the right to

Re: [GENERAL] Maximum of connections in PG

esult, drop > connection), they are pure, native applications which are keeping > connection from the start to the termination. > > Thank you! > > dd > > > > > 2017-03-14 15:29 GMT+01:00 Melvin Davidson <melvin6...@gmail.com>: > >> >> >> On Tue,

Re: [GENERAL] Maximum of connections in PG

users, you are better off using a connection pooler. My preference is for PgBouncer.https://pgbouncer.github.io/ <https://pgbouncer.github.io/>* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Unable to start postgresql

. A lot seems to > have changed since I started using Postgresql and I guess I should really > read up on how things work now and not in 2008! > > Regards, > > John > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your

Re: [GENERAL] Unable to start postgresql

l stop, change the port in postgresql.conf to 5433 (or 5434) and then attempt to restart, is your problem resolved? -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Unable to start postgresql

mailpref/pgsql-general > This is very suspicicious. >LOG: could not bind IPv4 socket: Cannot assign requested address >HINT: Is another postmaster already running on port 5432? If not, wait a f>ew seconds and retry. So check to see if the file "postmaster.pid" exists. If it does, and postgres is NOT running, just delet or rename it and try to start. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

o your subscription: > http://www.postgresql.org/mailpref/pgsql-general > In addition to what Tom said SELECT x'F'::bigint; works just fine. IOW, int = 4 bytes, while bigint = 8 bytes. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] ShmemAlloc maximum size

ed memory(15MB) but if i try with some small > memory(1MB), its working fine. > > Am i missing something.? > > > > cheers > *- *Harry > > >Am i missing something.? Probably. Perhaps if you told us the exact version of PostgreSQL & O/S, it might be more helpful. --

Re: [GENERAL] Move rows from one database to other

t;copy table_name". You can if you wrap it in a transaction: EG: BEGIN; COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( option [, ...] ) ] COMMIT; BEGIN; COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] COMMIT; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Move rows from one database to other

ostgresql.org/mailpref/pgsql-general >> > -- > > William Ivanski - Microsoft MVP > Depending on how much data you want to move, and if the tables have the same structure, you might also want to consider using pg_dump -a OR multiple instances of on satellite COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( option [, ...] ) ] on central COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] A BIG consideration is: Does the Central DB have the same table structures as all satellite DB's? Does the Central DB already have records in the tables. Do all Satellite tables have unique records for each other? As Adrian stated, it would be very helpful if you provided us with all O/S and PostgreSQL vesions involved. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] No space left on device

lobal.tmp": No space left >> on device >> 2017-02-19 03:46:53.819 UTC [24101]: [36927-1] host=,user=,db= LOG: could >> not open temporary statistics file "pg_stat_tmp/global.tmp": No space left >> on device >> 2017-02-19 03:46:53.913 UTC [24101]: [36928-1

Re: [GENERAL] postgresql how to duplicate rows in result.

-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > It would be immensely helpful if you provided the schema of tables involved with original query. In the meantime, I suggest you look into the use of UNION. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] clone_schema function

neral > Michael, Perhaps you can combine that code with the original by Emanuel '3manuek' and that I modified to be more inclusive of additional objects. See attached. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. -

Re: [GENERAL] Auto-Rollback option

> To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > In PgAdmin III, AUTO ROLLBACK and AUTO COMMIT are on by default in version 1.22.1 -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Auto-Rollback option

;> 1.22.1 version? PostgreSQL versions currently supported are 9.2.x to >> > > See above. > > 9.6.x >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > 1.22.1 version refers to PgAdmin3. It is not the PostgreSQL version. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Custom shuffle function stopped working in 9.6

> a > b > c > d > e > f > (6 rows) > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Making changes to PostgreSQL's configure logic so as to have contrib modules installed in a specific directory and make them use PGXS?

> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] psql only works with -h (even localhost)

t;> Thanks in advance >> >> >> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

www.postgresql.org/mailpref/pgsql-general > *I never got an answer to my question.* *Have you verified that postgresql.conf is the same of both 9.5 & 9.6?* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

allel Seq Scan on > > tblpuorderstatushistory osh (cost=0.00..2957011.17 rows=220702 > > width=4) > > > > > > > >Filter: ((cancelled_date IS > > NULL) AND (cc_accept_date IS NOT NULL) AND (vip_order_type = 17)) > > > > > > > >-> Index Scan using tblcnpatientordermap_pkey on > > tblcnpatientordermap po (cost=0.09..0.41 rows=1 width=8) > > > > > > > > Index Cond: (vip_order_id = os.vip_order_id) > > > > > > > > -> Nested Loop Semi Join (cost=0.17..1.02 rows=1 width=4) > > > >-> Index Scan using tblcndoctorpatientmap_pkey on > > tblcndoctorpatientmap d (cost=0.09..0.39 rows=1 width=8) > > > > Index Cond: (vip_patient_id = po.vip_patient_id) > > > >-> Index Scan using tblcnaccounts_pkey on > > tblcnaccounts a (cost=0.08..0.36 rows=1 width=4) > > > > Index Cond: (master_user_id = d.master_user_id) > > > > Filter: ((user_name)::text = 'dd'::text) > > > > (19 rows) > > > > > > > > > > > > Regards, > > > > ravi > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > >I am not sure whether they are doing the vacuum after the upgrade or not So just run an ANALYZE on the database! -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

Join (cost=0.17..1.02 rows=1 width=4) > > -> Index Scan using tblcndoctorpatientmap_pkey on > tblcndoctorpatientmap d (cost=0.09..0.39 rows=1 width=8) > > Index Cond: (vip_patient_id = po.vip_patient_id) > >-> Index Scan using tblcnaccounts_pkey on tblcnaccounts a > (cost=0.08..0.36 rows=1 width=4) > > Index Cond: (master_user_id = d.master_user_id) > > Filter: ((user_name)::text = 'dd'::text) > > (19 rows) > > > > > > Regards, > > ravi > 1. Have you run ANALYZE on the database after upgrading? 2. Have you insured that the proper changed were done to the postgresql.conf in 9.6? -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

On Thu, Jan 12, 2017 at 12:09 PM, Jonathan Vanasco <postg...@2xlp.com> wrote: > > On Jan 11, 2017, at 8:19 PM, Melvin Davidson wrote: > > >> > *Yes, you're right about ALTER SYSTER. Unfortunately, the op provided > neither PostgreSQL version or O/S, so we can't even

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

On Wed, Jan 11, 2017 at 8:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Jan 11, 2017 at 5:50 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco <postg...@2xlp.com> >>

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > *I can't confirm this, but have you tried :* *SELECT set_config('autovacuum', 'off'', false);* *SELECT pg_reload_conf(); * *note: you must be a superuser for above* -- *Melvin Davidson*

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 2:53 PM, Kevin Grittner <kgri...@gmail.com> wrote: > On Tue, Jan 10, 2017 at 1:36 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > > > IMHO, I disagree. I feel a better name would be "materialized > > table". > > The di

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 2:31 PM, Kevin Grittner <kgri...@gmail.com> wrote: > On Tue, Jan 10, 2017 at 12:44 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > > > fyi, a view is nothing more than just that, a view. > > A materialized view, afaic, is a misleading

Re: [GENERAL] Materialized view vs. view

l.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > fyi, a view is nothing more than just that, a view. A materialized view, afaic, is a misleading name, it is actually a valid table and you can create indexes on them, so theoretically you should be able to reduce response time on them. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] i got a process holding the lock

On Tue, Jan 10, 2017 at 1:28 PM, Edmundo Robles <edmu...@sw-argos.com> wrote: > ok, then is better kill one by one! > > On Tue, Jan 10, 2017 at 12:16 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> >> >> On Tue, Jan 10, 2017 at 1:07 PM, E

Re: [GENERAL] i got a process holding the lock

res: argos_admin > vacia 127.0.0.1(38601) PARSE waiting > > postgres 28995 2467 0 11:56 ?00:00:00 postgres: argos_admin > vacia 127.0.0.1(38603) PARSE waiting > > postgres 28999 2467 0 11:56 ?00:00:00 postgres: argos_admin > vacia 127.0.0.1(38605) PARSE waiting > > postgres 29002 2467 0 11:56 ?00:00:00 postgres: argos_admin > vacia 127.0.0.1(38608) PARSE waiting > > postgres 29003 2467 0 11:56 ?00:00:00 postgres: argos_admin > vacia 127.0.0.1(38609) PARSE waiting > > postgres 29005 2467 0 11:56 ?00:00:00 postgres: argos_admin > vacia 127.0.0.1(38611) PARSE waiting > > postgres 29013 2467 0 11:56 ?00:00:00 postgres: argos_admin > vacia 127.0.0.1(38615) SELECT waiting > > postgres 29046 2467 0 11:56 ?00:00:00 postgres: argos_admin > vacia 127.0.0.1(38634) SELECT waiting > > root 29071 6468 0 11:56 pts/200:00:00 grep postgres > > > On Tue, Jan 10, 2017 at 11:56 AM, Edmundo Robles <edmu...@sw-argos.com> > wrote: > >> how can i detect and cancel the process?? >> >> >> -- >> >> > > > -- > > Perhaps this scripts will help: *SELECT c.datname, c.pid as pid, c.client_addr, c.usename as user, c.query, CASE WHEN c.waiting = TRUETHEN 'BLOCKED'ELSE 'no'END as waiting, l.pid as blocked_by, c.query_start, current_timestamp - c.query_start as duration FROM pg_stat_activity c LEFT JOIN pg_locks l1 ON (c.pid = l1.pid and not l1.granted) LEFT JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted) LEFT JOIN pg_stat_activity l ON (l2.pid = l.pid) LEFT JOIN pg_stat_user_tables t ON (l1.relation = t.relid) WHERE pg_backend_pid() <> c.pidORDER BY datname, query_start;* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

st (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > *In addition to what Alban says, make sure you are comparing apples to apples.* *IOW, have you tuned the postgresql.conf on 9.6.1 to the same values as 8.4

Re: [GENERAL] Difficulty modelling sales taxes

On Mon, Jan 2, 2017 at 9:58 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 01/02/2017 06:38 AM, Melvin Davidson wrote: > >> >> On Mon, Jan 2, 2017 at 6:29 AM, Frank Millman <fr...@chagford.com >> <mailto:fr...@chagford.com>> wrote: >>

Re: [GENERAL] Difficulty modelling sales taxes

On Mon, Jan 2, 2017 at 6:29 AM, Frank Millman wrote: > > *From:* amul sul > *Sent:* Monday, January 02, 2017 12:42 PM > *To:* Frank Millman > *Cc:* pgsql-general > *Subject:* Re: [GENERAL] Difficulty modelling sales taxes > > > On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman

Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

ver, you can make roles database specific:https://www.postgresql.org/docs/9.4/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SECURITY <https://www.postgresql.org/docs/9.4/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SECURITY>* *db_user_namespace = on* *That being said, there is a trade off of managing multiple users & passwords VS simple access roles. * -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Book or other resource on Postgres-local code?

econd-edition <https://www.packtpub.com/big-data-and-business-intelligence/postgresql-9-administration-cookbook-second-edition>PostgreSQL Server Programminghttps://www.packtpub.com/big-data-and-business-intelligence/postgresql-server-programming-second-edition <https://www.packtpub.c

Re: [GENERAL] Securing Information

> To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > *Another thing to consider is DO NOT create your tables in the public schema.Instead, create a separate schema, then only grant access to that schema and tablesto users that are authorized to access

Re: [GENERAL] Avoid using index scan backward when limit order desc

ld of 45 for > the LIMIT ? Why not 50 ? 100 ? I may take the solution in my application to > have a LIMIT > 45 in order to prevent the performance issue, but am I sure > that this threshold will always be the same ? > > 2) Is it possible for a specific query to force the planner on choosing a > given index or preventing it from choosing one ? > > What kind of other options do I have to solve this performance issue ? > > Thanks in advance for any help, > > Regards, > > -- > Christophe Escobar > *You can temporarily disable index scanning for a session with* *SET enable_indexscan = off;* *and/orSET enable_indexonlyscan = off;* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] pgAdmin 4 - auto disconnect

than a tool to monitor PostgreSQL. However, you can kill all user processes (except your own) by submitting the following query. SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pg_backend_pid() <> pid; CAVEAT EMPTOR: You must be a superuser for this to work properly. -- *M

Re: [GENERAL] Concatenating NULL with JSONB value return NULL

}','{"attended": false}') ON CONFLICT (oid) DO UPDATE SET campaigns = EXCLUDED.campaigns, CASE WHEN fan.facts is NULL THEN facts = EXCLUDED.facts ELSE facts = fan.facts || EXCLUDED.facts END RETURNING *; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] SQL query problem of a Quiz program

r_id value AND question_id = 25 --> substitute any question_id value ORDER BY 1, 2, 3) SELECT * FROM quest WHERE id IN (SELECT max(id) FROM quest); -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] does postgres log the create/refresh of a materialized view anywhere?

On Tue, Dec 13, 2016 at 8:50 PM, Kevin Grittner <kgri...@gmail.com> wrote: > On Tue, Dec 13, 2016 at 7:37 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > > On Tue, Dec 13, 2016 at 7:36 PM, Kevin Grittner <kgri...@gmail.com> > wrote: > >> On Tue,

Re: [GENERAL] does postgres log the create/refresh of a materialized view anywhere?

s://www.postgresql.org/message-id/CANu8FiyiRPGZ+gB=1jayryx3hxcuqnflsfgdzfxsvlmj-jt...@mail.gmail.com Apparently the naysayers do not feel it is worthwhile. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Index size

On Sat, Dec 10, 2016 at 5:42 PM, Peter J. Holzer <hjp-pg...@hjp.at> wrote: > On 2016-12-09 21:45:35 -0500, Melvin Davidson wrote: > > On Fri, Dec 9, 2016 at 6:40 PM, Samuel Williams < > space.ship.travel...@gmail.com> > > wrote: > > >I also read that when

Re: [GENERAL] Index size

* *D. An index is REINDEXed * *Indexes point to the tid of the row for which the column(s) in the indexare involved. So if columns updated are not involved in the index, there is no need to change the index.https://www.postgresql.org/docs/9.4/static/sql-createindex.html <https://www.postgresql.org/docs/9.4/static/sql-createindex.html>https://www.postgresql.org/docs/9.4/static/sql-reindex.html <https://www.postgresql.org/docs/9.4/static/sql-reindex.html>* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Who dropped a role?

d it? > > 2.) If not dropped what happened with it? > > > > Does role dropping logged somewhere? > > > > Thanks for your help! > > > > dd > > > *In addition to the info on how to reset the role to login, you might want to make sure that, at

Re: [GENERAL] Problems Importing table to pgadmin

ection_data ( cc_20161207_pk bigint NOT NULL, election_id serial NOT NULL, election varchar(4), vote_type varchar(1) CONSTRAINT election_data_pk PRIMARY KEY (cc_20161207_pk, election_id) ); -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres pg_restore append data

_old x > table_old.sql6. Restore table_old to your database with your original table (table_orig).Then you can:INSERT INTO table_orig SELECT * FROM table_old WHERE your_primary_key NOT IN (SELECT your_primary_key FROM table_orig );* -- *Melvin Davidson* I reserve the right to fantasize.

Re: [GENERAL] Where would I be able to get instructions regarding postgresql installation on Windows 10?

I looked through the documentation But was not able to find any > information. If possible please guide me > > Varuna > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Index size

On Sun, Dec 4, 2016 at 4:43 AM, Samuel Williams < space.ship.travel...@gmail.com> wrote: > Melvin, of course there are differences. However, I suspect there are at > least SOME tangible differences which can be identified. > > On 4 December 2016 at 15:53, Melvin Davidson <

Re: [GENERAL] Index size

gt; Cheers, > Steve > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > You are comparing apples to oranges. MySQL and PostgreSQL engines are different b

Re: [GENERAL] Any work being done on materialized view?

inspector morse <inspectormors...@gmail.com> wrote: > Is there any work being done on materialized views for version 9.7? This > postgresql feature is severely lacking compared to similar features like > indexed views by sql server. > > > -- *Melvin Davidson* I reserv

Re: [GENERAL] Moving pg_xlog

le" Well, while the location of pg_xlog is not currently configurable, on Linux system the way to do it is to: 1. stop PostgreSQL 2. move the pg_xlog directory to a separate partition 3. create a symbolic link to point to the new partition 4. restart PostgreSQL -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Overwrite pg_catalog?

app-pgrestore.html>* *OR* *B:* *1: pg_dumpall from the cluster you want to copy https://www.postgresql.org/docs/9.6/static/app-pg-dumpall.html <https://www.postgresql.org/docs/9.6/static/app-pg-dumpall.html>* *2: use psql to restore the dump file into the new cluster https://www.postgr

Re: [GENERAL] Monitoring Replication - Postgres 9.2

ination server, and then copy in from there eg: Contents of bash script === #!/bin/bash psql -U postgres \t \f c \o results.csv select now() as time_pk, client_addr, state, sent_location, write_location, flush_location, replay_location, sync_priority from pg_stat_replication; \q scp results.csv destination_server/tmp/. psql -U postgres -h destination_server/tmp/. COPY data_table FROM '\tmp\results.csv' WITH csv; \q -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Monitoring Replication - Postgres 9.2

r_table SELECT now(), client_addr, state, sent_location, write_location, flush_location, replay_location, sync_priority from pg_stat_replication;*-- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] pg_dump system catalog

On Mon, Nov 28, 2016 at 10:50 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 11/28/2016 07:44 AM, Melvin Davidson wrote: > >> >> > >> >> *To clarify, you cannot dump the pg_catalog schema. It is the main >> control of how all other

Re: [GENERAL] pg_dump system catalog

g/docs/9.6/static/catalogs.html>pg_catalog itself is generated with the initdb command when a new postgresql cluster is generated.https://www.postgresql.org/docs/9.6/static/creating-cluster.html <https://www.postgresql.org/docs/9.6/static/creating-cluster.html>https://www.postgresql.org/docs/9.6/static

Re: [GENERAL] 'pg_ctl restart' does not terminate

s version 9.5 or above, then * *-m fast is the default so existing connections will not be a problem. * -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Invoice Table Design

ted! > > Rob > *Maybe it's just me, but I would go with a different design. tables:* * artistartist_uuidartist_type -- pro, basicartist_name artist...eventevent_uuid event_type -- wedding, birthday, etc; event_... invoiceinv_uuidinv_type -- event

[GENERAL] Thank you

to everyone.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] How to change order sort of table in HashJoin

rows) > > postgres=# > --- > > > Thanks and best regard! > *AFAIK, the only way to change a sort order is to use the ORDER BY clause in the SELECT.https://www.postgresql.org/docs/9.4/static/sql-select.html <https://www.postgresql.org/docs/9.4/static/sql-select.html>"8

Re: [GENERAL] pg_class (system) table increasing size.

_relation_size(n.nspname|| '.' || c.relname) as size_bytes, pg_total_relation_size(n.nspname|| '.' || c.relname) as total_size_bytes, CASE WHEN c.reltablespace = 0THEN 'pg_default'ELSE (SELECT t.spcname FROM pg_tablespace t WHERE (t.oid = c.re

Re: [GENERAL] which work memory parameter is used for what?

/www.postgresql.org/docs/9.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY <https://www.postgresql.org/docs/9.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY>* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Role and grants

t1 > (5 rows) > > With "\l" command It's no clear. > > > Finally, I don't find some command like "show grants for..." in MySQL. > > > Regards. > > Bryan > You can use the following query to show what table can access. Just replace wit

Re: [GENERAL] which work memory parameter is used for what?

> Mobile: +47 909 56 963 > andr...@visena.com > www.visena.com > <https://www.visena.com> > > *> There is no such thing in PG.>No implicit index will be createI believe he is referring to: >(including implicit index creation such as add foreign key).T

Re: [GENERAL] Must I create an index for child table if master table have one?

*To clarify, if you are talking about partitioning, then you almost certainly want to create a similar index on the child table.* *If you are referring to a Foreign Key Constraint, then it is also good idea to make that index, but not necessary.* On Tue, Nov 8, 2016 at 1:46 PM, Melvin Davidson

Re: [GENERAL] Must I create an index for child table if master table have one?

gt; child table. > > David J.​ > > >Indexes are self-contained within the physical table on which they are defined *IOW, yes. * -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] timestamp without timezone to have timezone

n |central |mountain| pacific+++ 2001-02-16 20:38:40-05 | 2001-02-16 21:38:40-05 | 2001-02-16 22:38:40-05 | 2001-02-16 23:38:40-05(1 row) * *-- * *Melvin Davidson* I re

Re: [GENERAL] Statistics on index usage

stat_all_indexes i JOIN pg_class c ON (c.oid = i.relid) JOIN pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_index idx ON (idx.indexrelid = i.indexrelid ) WHERE i.relname LIKE '%%' AND n.nspname NOT LIKE 'pg_%' ORDER BY 1, 2, 3; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Way to quickly detect if database tables/columns/etc. were modified?

On Mon, Oct 31, 2016 at 4:41 PM, Evan Martin <postgres...@realityexists.net> wrote: > On 31/10/2016 8:26 PM, Melvin Davidson wrote: > > I have tried using an event trigger to detect table creation (ie: > tg_event_audit_all ) however, that does not parse the schema_name a

Re: [GENERAL] Way to quickly detect if database tables/columns/etc. were modified?

On Mon, Oct 31, 2016 at 2:09 PM, John DeSoi <de...@pgedit.com> wrote: > > > On Oct 31, 2016, at 8:14 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > > > > That would certainly work, but the problem is, that trigger would have > to be created for ev

Re: [GENERAL] initdb createuser commands

On Mon, Oct 31, 2016 at 10:50 AM, Christofer C. Bell < christofer.c.b...@gmail.com> wrote: > On Sun, Oct 30, 2016 at 11:10 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> >> >> >> >> On Sun, Oct 30, 2016 at 8:08 PM, Samuel Williams <

Re: [GENERAL] Way to quickly detect if database tables/columns/etc. were modified?

On Mon, Oct 31, 2016 at 9:48 AM, Karsten Hilbert <karsten.hilb...@gmx.net> wrote: > On Mon, Oct 31, 2016 at 09:14:07AM -0400, Melvin Davidson wrote: > > >> Maybe create an event trigger that updates a simple table with the last > >> modification time or sends a noti

Re: [GENERAL] Way to quickly detect if database tables/columns/etc. were modified?

simple table with the last modification time or sends a notification?That would certainly work, but the problem is, that trigger would have to be created for every table in the database. When you have more than a couple dozen tables, as in hundreds, it becsmes a huge undertaking.*-- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] initdb createuser commands

. Simply renaming a command does not make it easier to learn or clarify it's use.* *That is the purpose of documentation. A beginner does not get a better understanding of command usage by the name of a command,* *they get it by actually using the command. In addition, I don't know any DBA that is in favor of longer command names (as you * *propose prefixing with pg_ ). The fact is, the commands are already self explanatory. The _only_ way to learn how to be a good DBA* *is to actually use the commands, and that also includes pg_ctl and psql commands. I agree that GUI tools make it easier to learn,* *but is essential to learn the command line tools and how to use. So again, it is not the name that is important, but the actual usage.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Way to quickly detect if database tables/columns/etc. were modified?

mplement version control software. However, that does not solve the problem of gremlins (developers) that like to play and make changes while bypassing CVS. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] initdb createuser commands

e all commands in question and renaming (or prefixing )them would do little to add to that. In fact, I'm pretty sure, existing DBA's like myself would be quite upset if commands were arbitrarily renamed.* *So as a wise man once said, If it ain't broke, don't fix it!* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Remove Standby (SLAVE) from Primary (MASTER) - Postgres9.1

On Fri, Oct 28, 2016 at 1:54 PM, Joanna Xu <joanna...@amdocs.com> wrote: > > All you have to do on the slave is: > > > *sudo su postgres* > > > *touch **/opt/postgres/9.1/data/trigger'* > > > > > Then the slave will go to standalone mode./ >

Re: [GENERAL] Remove Standby (SLAVE) from Primary (MASTER) - Postgres9.1

gt; > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com > > > > This message and the information contained herein is proprietary and > confidential and subject to the Amdocs policy statement, > > you may review at http://www.amdocs.com/email_disclaimer.

Re: [GENERAL] Drop user cascade

eral@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > >In general what stops us to do inter DBs connection like MSSQL? *It currently is not generic to PostgreSQL, but you can do that with the dblink extension/functions.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] pg_sample

temp\' || '/tmp/' || quote_ident(n.nspname) || '_' || quote_ident(c.relname) || '.csv' || || ' WITH CSV HEADER FORCE_QUOTE *;' FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r' AND relname NOT LIKE 'pg_%' AND relname NOT LIKE 'sql_%' LIMIT 100; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Dump all the indexes/constraints/roles

w.postgresql.org/docs/9.5/static/app-pgdump.html <https://www.postgresql.org/docs/9.5/static/app-pgdump.html>* *Also, please note you can use -a to only dump data* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Problem changing default data_directory in PG 9.6 + CentOS6

manually postmaster (because >>> service script doesn't understand postmaster.pid in the new data dir), >>> comment data_directory to use default place, start and connect to >>> pgAdmin3. Then, start/stop/start/etc run fine lookup for postmaster.pid >>> in /var/lib/pgsq

Re: [GENERAL] Problem changing default data_directory in PG 9.6 + CentOS6

the new data dir), comment > data_directory to use default place, start and connect to pgAdmin3. Then, > start/stop/start/etc run fine lookup for postmaster.pid in > /var/lib/pgsql/9.6/data. > What is the value of data_directory in postgresql.conf ? Is there anything in /var/log/postgresq

Re: [GENERAL] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

an idle system. Pretty fast. Sure on an idle system, you will get a table lock right away, but OP's statements imply a large busy system. And if there are transactions occurring against that table, there is no telling how long it will take. Since we do not have enough specific info, I stan

Re: [GENERAL] My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

to help. IE: Which version of PostgreSQL? What are the memory parameters in postgresql.conf (shared_memory, max_connections, work_mem, maintenance_work_mem, etc..) ? How big are the tables being moved? What type of disks etc. FYI, moving between tablespaces requires an exclusive table lock, so i

Re: [SPAM] [GENERAL] psql 9.3 automatic recovery in progress

ges to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > The general philosphy is to start by setting shared_memory to 1/4 system memory, so shared_buffers should be 20480 MB -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Multi tenancy : schema vs databases

st solution is to isolate by a client ID in the tables of one database. Then make sure you have sufficient and correct security on those tables.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Time travel?

On Thu, Sep 29, 2016 at 3:20 PM, Thomas Munro <thomas.mu...@enterprisedb.com > wrote: > On Fri, Sep 30, 2016 at 8:17 AM, Kevin Grittner <kgri...@gmail.com> wrote: > > On Thu, Sep 29, 2016 at 2:06 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > > > >

[GENERAL] Time travel?

/d-log/pg_xlog/0001000D00C9How is it possible for the WAL file to be accessed BEFORE it was created?Am I overlooking something simple?* *Melvin Davidson*

Re: [GENERAL] how to monitor the progress of really large bulk operations?

time in the future". *https://postgresql.uservoice.com/forums/21853-general/suggestions/249745-query-progress-reported-by-percentage-in-pg-stat <https://postgresql.uservoice.com/forums/21853-general/suggestions/249745-query-progress-reported-by-percentage-in-pg-stat>* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Re: [GENERAL] Determining server load

the database itself track this metric. If not, well, I > guess that's another project :) > > --- > Israel Brewster > Systems Analyst II > Ravn Alaska > 5245 Airport Industrial Rd > Fairbanks, AK 99709 > (907) 450-7293 > --

Re: [GENERAL] Determining server load

- > >> > >> > >> > >> > >> > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

<    1   2   3   4   5   6   7   >