Re: Two things bit baffling in RDS PG
On Thu, May 3, 2018 at 1:18 PM, Ravi Krishnawrote: > I am playing around with RDS PG and I am not able to understand the > following: > > 1. The database name I created via RDS console is in upper case with no > quotes. From the remote machine via psql, > if I try to use lower case db name with the -d option it errors out > "database not found". Works only with upper case. > This behavior does not exist in non RDS env. > Their console is apparently case-sensitive, creating the database with the exact capitalization you supplied. Basically it puts whatever you type into double-quotes. I see nothing wrong with that decision. > > 2. Two RDS parameter group > > rds.force_ssl > ssl > have been set to 1 (true) > > yet from a remote machine I can connect to the database via psql > without specifying any option for ssl. How is it doing ? > > psql (10.3 (Debian 10.3-1.pgdg90+1)) > SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, > bits: 256, compression: off) > psql uses SSL if it detects the possibility during the connection handshake. This isn't RDS-specific. https://www.postgresql.org/docs/10/static/ssl-tcp.html David J.
Two things bit baffling in RDS PG
I am playing around with RDS PG and I am not able to understand the following: 1. The database name I created via RDS console is in upper case with no quotes. From the remote machine via psql, if I try to use lower case db name with the -d option it errors out "database not found". Works only with upper case. This behavior does not exist in non RDS env. 2. Two RDS parameter group rds.force_ssl ssl have been set to 1 (true) yet from a remote machine I can connect to the database via psql without specifying any option for ssl. How is it doing ? psql (10.3 (Debian 10.3-1.pgdg90+1)) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) thanks
Re: relkind='p' has no pg_stat_user_tables
On Thu, May 03, 2018 at 11:15:19AM -0700, Adrian Klaver wrote: > On 05/03/2018 10:38 AM, Justin Pryzby wrote: > >On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote: > >>On 05/03/2018 09:20 AM, Alvaro Herrera wrote: > https://www.postgresql.org/docs/10/static/sql-createtable.html > > "A partitioned table is divided into sub-tables (called partitions), which > are created using separate CREATE TABLE commands. The partitioned table is > itself empty. A data row inserted into the table is routed to a partition > based on the value of columns or expressions in the partition key. ... " > >>> > >>>Yeah, but I think Justin has a valid question from the POV of the user: > >>>how can we figure out if we need to re-run analyze on a partitioned > >>>table, if the time of last analyze is not stored anywhere? > >> > >>I agree. The only thing I can think of is, that knowing : > >> > >>ANALYZE VERBOSE t; > >> > >>walks the inheritance tree, look at the pg_stat_user_tables for one of the > >>children for the last time analyzed. > > > >I think I can make this work for my purposes: > > > >SELECT MIN(GREATEST(last_analyze,last_autoanalyze)) > >FROM pg_stat_user_tables psut > >JOIN pg_inherits i > >ON i.inhrelid=psut.relid > >WHERE i.inhparent=... > > > >I was about to say that it's perhaps more correct for relkind='r' parents, > >too. > > > >But actually, it looks like for relkind='p', ANALYZE populates stats on child > >tables in addition to the parent. For relkind='r', the behavior (introduced > >in > >PG9.0 as I recall) is that ANALYZE on parent creates stats only for parent > >(both "inherited" stats including children, and "ONLY" stats for the > >potentially-nonempty parent). > > > >I guess ability to update child tables' stats is a nice feature, but I'm > >surprised. I wonder if that was a deliberate/documented change ? > > I was with you until I got to the above. You seem to be comparing apples and > oranges unless I am missing something. Yes, I was surprised about the difference between ANALYZE relkind_p and relkind_r. But I see that's a documented behavior I'd missed until now: https://www.postgresql.org/docs/current/static/sql-analyze.html |If the specified table is a partitioned table, both the inheritance statistics |of the partitioned table as a whole and statistics of the individual partitions |are updated. Thanks, Justin
Re: relkind='p' has no pg_stat_user_tables
On 05/03/2018 10:38 AM, Justin Pryzby wrote: On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote: On 05/03/2018 09:20 AM, Alvaro Herrera wrote: https://www.postgresql.org/docs/10/static/sql-createtable.html "A partitioned table is divided into sub-tables (called partitions), which are created using separate CREATE TABLE commands. The partitioned table is itself empty. A data row inserted into the table is routed to a partition based on the value of columns or expressions in the partition key. ... " Yeah, but I think Justin has a valid question from the POV of the user: how can we figure out if we need to re-run analyze on a partitioned table, if the time of last analyze is not stored anywhere? I agree. The only thing I can think of is, that knowing : ANALYZE VERBOSE t; walks the inheritance tree, look at the pg_stat_user_tables for one of the children for the last time analyzed. I think I can make this work for my purposes: SELECT MIN(GREATEST(last_analyze,last_autoanalyze)) FROM pg_stat_user_tables psut JOIN pg_inherits i ON i.inhrelid=psut.relid WHERE i.inhparent=... I was about to say that it's perhaps more correct for relkind='r' parents, too. But actually, it looks like for relkind='p', ANALYZE populates stats on child tables in addition to the parent. For relkind='r', the behavior (introduced in PG9.0 as I recall) is that ANALYZE on parent creates stats only for parent (both "inherited" stats including children, and "ONLY" stats for the potentially-nonempty parent). I guess ability to update child tables' stats is a nice feature, but I'm surprised. I wonder if that was a deliberate/documented change ? I was with you until I got to the above. You seem to be comparing apples and oranges unless I am missing something. The behavior for 'r' tables has not changed: https://www.postgresql.org/docs/9.0/static/sql-analyze.html https://www.postgresql.org/docs/10/static/sql-analyze.html The 'p' type table does not appear until version 10: https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html https://www.postgresql.org/docs/10/static/catalog-pg-class.html so there is no past behavior to compare to. Justin -- Adrian Klaver adrian.kla...@aklaver.com
Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN
On 05/03/2018 09:47 AM, George Neuner wrote: On Wed, 2 May 2018 16:01:01 -0700, Adrian Klaverwrote: On 05/02/2018 02:29 PM, Jim Michaels wrote: - the microsoft patented CSV would be required for implementation. it handles special data with commas and double-quotes in them Huh?: https://en.wikipedia.org/wiki/Comma-separated_values#History Disclaimer ... I haven't investigated the claim. Difficult because it is made up of dreams, wishes and nightmares:) However, I would not discount the possibility that Microsoft really has patented some variation of CSV. They absolutely did *try* to copyright the use of + and - symbols for specifying addition and subtraction operations in VisualBASIC. Not seeing it: http://patft.uspto.gov/netacgi/nph-Parser?Sect1=PTO2=HITOFF=1=%2Fnetahtml%2FPTO%2Fsearch-bool.html=0=S=50=microsoft=AANM=AND=csv==PTXT It's possible that they slipped something past the examiners. But more likely the use of a CSV-like format was specified to be part of a larger process. In that case the format itself might not be claimed, but rather only the *use* of the format for some specific purpose. IANAL, George -- Adrian Klaver adrian.kla...@aklaver.com
Re: relkind='p' has no pg_stat_user_tables
On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote: > On 05/03/2018 09:20 AM, Alvaro Herrera wrote: > >>https://www.postgresql.org/docs/10/static/sql-createtable.html > >> > >>"A partitioned table is divided into sub-tables (called partitions), which > >>are created using separate CREATE TABLE commands. The partitioned table is > >>itself empty. A data row inserted into the table is routed to a partition > >>based on the value of columns or expressions in the partition key. ... " > > > >Yeah, but I think Justin has a valid question from the POV of the user: > >how can we figure out if we need to re-run analyze on a partitioned > >table, if the time of last analyze is not stored anywhere? > > I agree. The only thing I can think of is, that knowing : > > ANALYZE VERBOSE t; > > walks the inheritance tree, look at the pg_stat_user_tables for one of the > children for the last time analyzed. I think I can make this work for my purposes: SELECT MIN(GREATEST(last_analyze,last_autoanalyze)) FROM pg_stat_user_tables psut JOIN pg_inherits i ON i.inhrelid=psut.relid WHERE i.inhparent=... I was about to say that it's perhaps more correct for relkind='r' parents, too. But actually, it looks like for relkind='p', ANALYZE populates stats on child tables in addition to the parent. For relkind='r', the behavior (introduced in PG9.0 as I recall) is that ANALYZE on parent creates stats only for parent (both "inherited" stats including children, and "ONLY" stats for the potentially-nonempty parent). I guess ability to update child tables' stats is a nice feature, but I'm surprised. I wonder if that was a deliberate/documented change ? Justin
Re: relkind='p' has no pg_stat_user_tables
On 05/03/2018 09:20 AM, Alvaro Herrera wrote: Adrian Klaver wrote: and from here: https://www.postgresql.org/docs/10/static/sql-createtable.html "A partitioned table is divided into sub-tables (called partitions), which are created using separate CREATE TABLE commands. The partitioned table is itself empty. A data row inserted into the table is routed to a partition based on the value of columns or expressions in the partition key. ... " Yeah, but I think Justin has a valid question from the POV of the user: how can we figure out if we need to re-run analyze on a partitioned table, if the time of last analyze is not stored anywhere? I agree. The only thing I can think of is, that knowing : ANALYZE VERBOSE t; walks the inheritance tree, look at the pg_stat_user_tables for one of the children for the last time analyzed. Using psql -E and \d+ on table t I got the following to find the children: test_(aklaver)> SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid) FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '1417272' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text; oid |pg_get_expr -+ t1 | FOR VALUES FROM (1) TO (9) -- Adrian Klaver adrian.kla...@aklaver.com
Re: relkind='p' has no pg_stat_user_tables
On 05/03/2018 08:45 AM, Justin Pryzby wrote: On Thu, May 03, 2018 at 07:44:24AM -0700, Adrian Klaver wrote: On 05/03/2018 07:14 AM, Justin Pryzby wrote: I (finally) realized that my script for ANALYZEing parents of table hierarchies every month or so was looping around the same parent tables every night due to no stats for date of last last analysis. Would help to see the script. I reproduced it more simply than the 300 line script: postgres=# CREATE TABLE t(i int)PARTITION BY RANGE(i); postgres=# CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (9); postgres=# INSERT INTO t1 VALUES(1),(2); postgres=# ANALYZE VERBOSE t; I would say the answer lies below from above command: test_(postgres)# ANALYZE VERBOSE t; INFO: analyzing "public.t" inheritance tree INFO: "t1": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows INFO: analyzing "public.t1" INFO: "t1": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows ANALYZE and from here: https://www.postgresql.org/docs/10/static/sql-createtable.html "A partitioned table is divided into sub-tables (called partitions), which are created using separate CREATE TABLE commands. The partitioned table is itself empty. A data row inserted into the table is routed to a partition based on the value of columns or expressions in the partition key. ... " test_(postgres)# select * from only t; i --- (0 rows) Table t is just a pointer to the child tables and only the bulk statistics as shown in pg_statistic are maintained. postgres=# SELECT * FROM pg_stat_user_tables WHERE relname='t'; (0 rows) postgres=# SELECT 1 FROM pg_statistic WHERE starelid='t'::regclass; ?column? | 1 Justin -- Adrian Klaver adrian.kla...@aklaver.com
Re: relkind='p' has no pg_stat_user_tables
On 05/03/2018 07:14 AM, Justin Pryzby wrote: I (finally) realized that my script for ANALYZEing parents of table hierarchies every month or so was looping around the same parent tables every night due to no stats for date of last last analysis. Would help to see the script. I guess that's deliberate/known and maybe related to relkind='p' having no relfilenode. Not sure that is an issue as pg_stat_user uses relid not relfilenode: https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW For reasons why they are often not the same: https://www.postgresql.org/docs/10/static/storage-file-layout.html " Caution Note that while a table's filenode often matches its OID, this is not necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER and some forms of ALTER TABLE, can change the filenode while preserving the OID. Avoid assuming that filenode and table OID are the same. Also, for certain system catalogs including pg_class itself, pg_class.relfilenode contains zero. The actual filenode number of these catalogs is stored in a lower-level data structure, and can be obtained using the pg_relation_filenode() function. " Is there any good workaround other than making stampfiles or making my own "last analyzed" table? Thanks, Justin -- Adrian Klaver adrian.kla...@aklaver.com
relkind='p' has no pg_stat_user_tables
I (finally) realized that my script for ANALYZEing parents of table hierarchies every month or so was looping around the same parent tables every night due to no stats for date of last last analysis. I guess that's deliberate/known and maybe related to relkind='p' having no relfilenode. Is there any good workaround other than making stampfiles or making my own "last analyzed" table? Thanks, Justin
Re: Wanted: postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb
oh! I was late! well..you have one extra link now! :) On 03/05/18 11:00, Fabio Pardi wrote: > Hi Craig, > > > are those good? > > > http://mirror1.net.one.com/postgres/pool/main/p/postgresql-9.6/postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb > > http://atalia.postgresql.org/morgue/p/postgresql-9.6/postgresql-9.6-dbg_9.6.5-1.pgdg80%2b1_amd64.deb > > > regards, > > fabio pardi > > > > On 03/05/18 10:23, Craig Ringer wrote: >> Hi all >> >> I'm trying to debug a PostgreSQL install with a very hard to reproduce >> bug. The user did not install debug info, and apt.postgresql.org has >> purged the packages. 2ndQuadrant doesn't yet have a mirror of all >> historical packages up and running (though we're working on it). >> >> So I need postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb . >> >> If anyone has it in a mirror repo, their /var/cache/apt/archives/, or >> even has it installed and can supply the files it contains, I'd be >> extremely grateful. >> >
Re: Wanted: postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb
Hi Craig, are those good? http://mirror1.net.one.com/postgres/pool/main/p/postgresql-9.6/postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb http://atalia.postgresql.org/morgue/p/postgresql-9.6/postgresql-9.6-dbg_9.6.5-1.pgdg80%2b1_amd64.deb regards, fabio pardi On 03/05/18 10:23, Craig Ringer wrote: > Hi all > > I'm trying to debug a PostgreSQL install with a very hard to reproduce > bug. The user did not install debug info, and apt.postgresql.org has > purged the packages. 2ndQuadrant doesn't yet have a mirror of all > historical packages up and running (though we're working on it). > > So I need postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb . > > If anyone has it in a mirror repo, their /var/cache/apt/archives/, or > even has it installed and can supply the files it contains, I'd be > extremely grateful. >
Re: Wanted: postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb
On 3 May 2018 at 16:46, Karsten Hilbertwrote: > On Thu, May 03, 2018 at 04:23:14PM +0800, Craig Ringer wrote: > >> I'm trying to debug a PostgreSQL install with a very hard to reproduce >> bug. The user did not install debug info, and apt.postgresql.org has >> purged the packages. 2ndQuadrant doesn't yet have a mirror of all >> historical packages up and running (though we're working on it). >> >> So I need postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb . >> >> If anyone has it in a mirror repo, their /var/cache/apt/archives/, or >> even has it installed and can supply the files it contains, I'd be >> extremely grateful. > > This is the closest I found with a quick searc > > > http://snapshot.debian.org/archive/debian/20170831T163230Z/pool/main/p/postgresql-9.6/postgresql-9.6-dbg_9.6.5-1_amd64.deb > > Not sure this is close enough though. Nope, but solved. http://atalia.postgresql.org/morgue/ is what I was looking for. It's linked to via this post https://www.postgresql.org/message-id/20160731194944.amiwidhsoqh4osac%40msg.df7cb.de which is linked from apt.postgresql.org, so I'm blind. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Wanted: postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb
On Thu, May 03, 2018 at 04:23:14PM +0800, Craig Ringer wrote: > I'm trying to debug a PostgreSQL install with a very hard to reproduce > bug. The user did not install debug info, and apt.postgresql.org has > purged the packages. 2ndQuadrant doesn't yet have a mirror of all > historical packages up and running (though we're working on it). > > So I need postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb . > > If anyone has it in a mirror repo, their /var/cache/apt/archives/, or > even has it installed and can supply the files it contains, I'd be > extremely grateful. This is the closest I found with a quick searc http://snapshot.debian.org/archive/debian/20170831T163230Z/pool/main/p/postgresql-9.6/postgresql-9.6-dbg_9.6.5-1_amd64.deb Not sure this is close enough though. Karsten --
Wanted: postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb
Hi all I'm trying to debug a PostgreSQL install with a very hard to reproduce bug. The user did not install debug info, and apt.postgresql.org has purged the packages. 2ndQuadrant doesn't yet have a mirror of all historical packages up and running (though we're working on it). So I need postgresql-9.6-dbg_9.6.5-1.pgdg80+1_amd64.deb . If anyone has it in a mirror repo, their /var/cache/apt/archives/, or even has it installed and can supply the files it contains, I'd be extremely grateful. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services