Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates
On 03/18/2013 12:07 AM, Craig Ringer wrote: So this problem is verified. Thanks for taking the time to look into this. Good to know I'm not crazy. What we need to happen instead is for root.crt to contain only the trusted certificates and have a *separate* file or directory for intermediate certificates that OpenSSL can look up to get the intermediates it needs to validate client certs, like `ssl_ca_chain_file` or `ssl_ca_chain_path` if we want to support OpenSSL's hashed certificate directories. I did a fair bit of searching and asked about this subject on the openssl-users list. The consensus seems to be that doing this with OpenSSL will require 2 separate sets of certificates and a validation callback. The two sets of certificates are: * Trusted certificates - What currently goes in the (unfortunately named) root.crt file. * Validation-only certificates - CA certificates that are used only to complete the chain from a trusted certificate to a self-signed root. I haven't been able to come up with a particularly good name for a file containing this type of certificate(s) -- validate.crt? All of the certificates in both sets get added to the SSL_CTX, so that OpenSSL can do its normal validation -- all the way to a root CA. The trusted certificates also need to be maintained as a separate set (X509_STORE?). Once OpenSSL has built the complete certificate chain, the validation callback can refuse the connection if the chain does not contain at least one of the *trusted* certificates. This is conceptually simple, and I've been fiddling with it for the last week or so. Unfortunately, the OpenSSL documentation has made this far more challenging that it should be. Simple things like reading multiple certificates from a file, checking whether an X509_STORE contains a particular certificate, etc. are all proving to be unexpectedly difficult. (I never thought that I'd miss the Java SSL API!) System wide installation of the root may allow OpenSSL to discover it and use it for verification back to the root without having to trust it to sign clients. I'll do some more checking to see if this is possible with how Pg uses OpenSSL but I'm inclined to doubt it. Me too. I think that OpenSSL's behavior embodies the idea that a certificate can only be validated if a chain can be formed to a self- signed root CA. (And there's probably a pretty good argument to be made for this position, particularly when CRLs are added to the mix.) I thought you might be able to add the common root to the server.crt certificate chain to let OpenSSL discover it that way, but it looks like OpenSSL won't use certs it's seen in server.crt when verifying client cert trust paths. Nope. It's pretty obvious from be-secure.c that only the certificates in root.crt will be used. -- Ian Pilcher arequip...@gmail.com Sometimes there's nothing left to do but crash and burn...or die trying. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates
On 03/18/2013 01:07 PM, Craig Ringer wrote:
System wide installation of the root may allow OpenSSL to discover it
and use it for verification back to the root without having to trust it
to sign clients. I'll do some more checking to see if this is possible
with how Pg uses OpenSSL but I'm inclined to doubt it.
It looks like we aren't reading the system-wide certs or looking them up
when certs aren't resolved in the files Pg explicitly passes to OpenSSL,
so a system-wide install doesn't look like it'll work.
I've had a look at how Apache handles this and it took me a while to
work out what's going on. Apache uses SSLCACertificateFile (concatenated
certs) / SSLCACertificatePath (hashed dir) to look up trusted
certificate signers. It According to the docs it doesn't appear to make
any provision there for trusting intermediate certificates but not their
parents as signers of client certificates, but it looks like support is
there, the docs just don't explain it well.
Apache has SSLCADNRequestFile / SSLCADNRequestPath which are described
as controlling the acceptable certificate names sent to clients in a
client cert request. The docs don't make it clear if Apache will trust
only client certs with these certs in their chains or whether this only
controls the list of certificate DNs presented to the client rather than
what's accepted in response. The code suggests that they control trust
not just the cert list presented.
In Apache's modules/ssl/ssl_engine_init.c it calls
SSL_CTX_load_verify_locations on the SSLCACertificateFile and
SSLCACertificatePath.
It then calls ssl_init_FindCAList with the
SSLCADNRequestFile/SSLCADNRequestPath if they're specified in the
configuration, otherwise it calls it with the
SSLCACertificateFile/SSLCACertificatePath . That calls
ssl_init_PushCAList on all of the certs it finds in the File and Path
variants. For each cert file that calls SSL_load_client_CA_file and for
each cert within each file pushes the cert onto a STACK_OF(X509_NAME) if
a cert with the same DN isn't already in the stack. It passes the stack
to OpenSSL's SSL_CTX_set_client_CA_list .
So what Apache does appears to boil down to:
SSL_CTX_load_verify_locations(ca_file,ca_path);
if (ca_dn_file || ca_dn_path) {
SSL_CTX_set_client_CA_list( ... STACK_OF unique certs on ca_dn_file
and ca_dn_path ... );
} else {
SSL_CTX_set_client_CA_list( ... STACK_OF unique certs on ca_file and
ca_path );
}
This appears to match Ian's description of having a validation-only cert
list and a separate list of certs used to verify clients. I'd like to
follow Apache's model:
in postgresql.conf, if ssl_ca_file is set then pass it to
SSL_CTX_load_verify_locations . If the proposed new parameter
ssl_ca_valid_client_signers_file is set then pass the certs in that to
SSL_CTX_set_client_CA_list ; otherwise pass the certs in ssl_ca_file to
SSL_CTX_set_client_CA_list and thus retain the current behaviour.
Hopefully we can avoid the ugly read-and-deduplicate stuff Apache has to
do because we currently only support a certfile anyway, we don't read
certdirs, so I'll look for helper functions that wrap
SSL_CTX_set_client_CA_list.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates
On 03/18/2013 02:27 PM, Ian Pilcher wrote: On 03/18/2013 12:07 AM, Craig Ringer wrote: So this problem is verified. * Trusted certificates - What currently goes in the (unfortunately named) root.crt file. Well, a little unfortunate. It contains roots of *client authentication* trust, which is fair enough, they just aren't necessarily self-signed certificates that are roots of *certificate validity* trust (root CA certs). This list is set by SSL_CTX_set_client_CA_list . The examples section of its man page contains: Scan all certificates in CAfile and list them as acceptable CAs: SSL_CTX_set_client_CA_list(ctx,SSL_load_client_CA_file(CAfile)); * Validation-only certificates - CA certificates that are used only to complete the chain from a trusted certificate to a self-signed root. I haven't been able to come up with a particularly good name for a file containing this type of certificate(s) -- validate.crt? We should probably take advantage of the fact that 9.2 made these filenames configurable to deprecate root.crt and choose two descriptive filenames, something like trusted_cert_roots.crt and trusted_client_cert_signers.crt . This is conceptually simple, and I've been fiddling with it for the last week or so. Unfortunately, the OpenSSL documentation has made this far more challenging that it should be. Simple things like reading multiple certificates from a file, checking whether an X509_STORE contains a particular certificate, etc. are all proving to be unexpectedly difficult. (I never thought that I'd miss the Java SSL API!) Apache's sources are useful there. When working with OpenSSL sometimes the sanest option is to find something you know already does it right, work out how, *understand why it works* and then apply that approach to your code. Blindly copying their approach is stupid and guaranteed to lead to security holes, but others' code remains some of the best documentation for OpenSSL if used for hints rather than blindly copied. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C++Builder table exist
Thanks. Case-folding was my problem.
Is there any way of getting PostgreSQL to work according to the SQL standard
(The folding of
unquoted names to lower case in PostgreSQL is incompatible with the SQL
standard, which says that unquoted names should be folded to
upper case.), so there is no need for me to add quotes to all names?
To: pgsql-general@postgresql.org
From: ja...@xnet.co.nz
Subject: Re: [GENERAL] C++Builder table exist
Date: Sat, 16 Mar 2013 06:16:33 +
On 2013-03-13, Charl Roux charl.r...@hotmail.com wrote:
--_51d77859-0e03-4afa-bde6-853bee9c0a11_
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
appologies for the formatting, gmane did something to your email that
SLRN didn't like.
void __fastcall TfrmMain::FormCreate(TObject *Sender)
{
int errorCode;
TStringList *tableList = new TStringList;
frmDataModule-eyeConnection-GetTableNames(tableList);
// create queryBackup table if does not exist
AnsiString SQL = CREATE TABLE queryBackup( queryName VARCHAR(30) PRIMARY
KEY ,query VARCHAR(1));
if( tableList-IndexOf(queryBackup) 0 )
errorCode = frmDataModule-eyeConnection-ExecuteDirect(SQL);
}
ERROR: relation querybackup already exists.
The problem is you're checking for queryBackup and then creating
querybackup when it doesn't exist. (note: capitalisation)
http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html
This case-folding is one of the few places where postgres deliberately
breaks the sql standard (AIUI standard wants case folded upwards).
--
⚂⚃ 100% natural
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C++Builder6 enum
Thanks. Case-folding was my problem. Is there any way of getting PostgreSQL to work according to the SQL standard (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case.), so there is no need for me to add quotes to all names? Date: Fri, 15 Mar 2013 07:05:11 -0700 From: adrian.kla...@gmail.com To: charl.r...@hotmail.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] C++Builder6 enum On 03/15/2013 04:06 AM, Charl Roux wrote: Hi, I am using C++Builder6 on WinXPSP3, with Devart's dbExpress driver. I declared a enumeration type called 'professionEnum', which is used by my variable 'profession'. I get an error: 'Cannot access field 'rootcause' as type Text', if I try and enter a value into the enum field. Not sure where 'professionEnum' comes into play, it not shown anywhere below. I will assume you mean 'rootCauseEnum'. 1. I create the table as follows: CREATE TABLE IF NOT EXISTS status( statusCode BIGINT PRIMARY KEY, description VARCHAR(50), level SMALLINT, rootCause rootCauseEnum, material VARCHAR(100), actionTaken VARCHAR(50) ) 2. I add an enum type 'rootCauseEnum' as follows: AnsiString SQL; SQL = CREATE TYPE ; SQL = SQL + edtEnumerationType-Text;// My entry: rootCauseEnum SQL = SQL + AS ENUM ( ; SQL = SQL + edtEnumerationList-Text; // My entry: 'none','unknown','elec','oil' SQL = SQL + ); int errorCode = frmDataModule-eyeConnection-ExecuteDirect(SQL); 3. I add a record to the status table from the command prompt which works fine as follows: eye=# INSERT INTO status VALUES( 12, 'Running', 0, 'none', 'Not applicable', 'NA'); 4.I add a record to the status table from C++Builder as follows: I have an edit box for every field, as I enter the following all is fine: statusCode=13 description=Stopped level=0 As I enter any of the following into the rootCause field: rootCause=none rootCause=1 I get the following error: Cannot access field 'rootcause' as type Text. Are you sure this is not a case sensitivity problem on the field rootCause. In your table definition it is rootCause, in the error message it is rootcause. Postgres will fold down unless the field name is quoted. If the the original table definition quoted the field names then they retain their case and need to be quoted when used. For more detail see: http://www.postgresql.org/docs/9.2/static/sql-syntax-lexical.html 4.1.1. Identifiers and Key Words Therefore before I apply the updates to the table, i get the error. The field 'rootCause' is indicated as (Memo) in run-time. This worked fine when I used MySQL. With MySQL I can enter rootCause=none or rootCause=1 Thanks. Charl -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] C++Builder table exist
Charl Roux wrote: Is there any way of getting PostgreSQL to work according to the SQL standard (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case.), so there is no need for me to add quotes to all names? No, PostgreSQL will always ford to lowercase. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Addled index
On 17 Mar 2013, at 04:30, Tom Lane t...@sss.pgh.pa.us wrote: Oleg Alexeev oalex...@gmail.com writes: * it is varchar columns, 256 and 32 symbols length * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8 * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms, commit_delay = 100, commit_siblings = 10, checkpoint_timeout = 20min, checkpoint_completion_target = 0.7 * postgres 9.2.3 installed via yum repository for version 9.2 * 64 bit Centos 6, installed and updated from yum repository fsync off? Have you had any power failures or other system crashes? ext4 is *way* more prone than ext3 was to corrupt data when fsync is disabled, because it caches and reorders writes much more aggressively. Database located on software md raid 1 based on two SSD disks array. Ext4 filesystem. Database is master node. Meh. I quote from the RHEL6 documentation (Storage Administration Guide, Chapter 20: Solid-State Disk Deployment Guidelines): Red Hat also warns that software RAID levels 1, 4, 5, and 6 are not recommended for use on SSDs. https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/6/html/Storage_Administration_Guide/newmds-ssdtuning.html The part of the docs I'm looking at only asserts that performance is bad, but considering that it's a deprecated combination, it may well be that there are data-loss bugs in there. I'd certainly suggest making sure you are on a *recent* kernel. If that doesn't help, reconsider your filesystem choices. Yeah, I don't think I'd consider using software raid for SSDs any time a good idea -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates
Craig, all, * Craig Ringer (cr...@2ndquadrant.com) wrote: PROBLEM VERIFIED Let me just say ugh. I've long wondered why we have things set up in such a way that the whole chain has to be in one file, but it didn't occur to me that it'd actually end up causing this issue. In some ways, I really wonder about this being OpenSSL's fault as much as ours, but I doubt they'd see it that way. :) What we need to happen instead is for root.crt to contain only the trusted certificates and have a *separate* file or directory for intermediate certificates that OpenSSL can look up to get the intermediates it needs to validate client certs, like `ssl_ca_chain_file` or `ssl_ca_chain_path` if we want to support OpenSSL's hashed certificate directories. Makes sense to me. I'm not particular about the names, but isn't this set of CAs generally considered intermediary? Eg: 'trusted', ' intermediate', etc? Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] C++Builder table exist
On 03/18/2013 03:25 AM, Charl Roux wrote: Thanks. Case-folding was my problem. Is there any way of getting PostgreSQL to work according to the SQL standard (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case.), so there is no need for me to add quotes to all names? As far I know the only difference would be whether the table name you get in the error comes back as lower case or upper case. In your situation where you are using a quoted mixed case both folding styles would lead to an error. In that aspect the SQL standard and Postgres follow the same rule, if the name is quoted on creation the case it was quoted in must be maintained on subsequent use. If you do not want to quote identifiers on use then do not quote then on creation: test= create TABLE case_test(MixedCaseQuoted varchar, MixedCaseUnQuoted varchar); CREATE TABLE test= \d+ case_test Table utility.case_test Column | Type| Modifiers | Storage | Description ---+---+---+--+- MixedCaseQuoted | character varying | | extended | mixedcaseunquoted | character varying | | extended | Has OIDs: no test= SELECT mixedcasequoted from case_test ; ERROR: column mixedcasequoted does not exist LINE 1: SELECT mixedcasequoted from case_test ; ^ test= SELECT mixedcaseunquoted from case_test ; mixedcaseunquoted --- (0 rows) test= SELECT MixedCaseQuoted from case_test ; MixedCaseQuoted - (0 rows) test= SELECT MixedCaseUnQuoted from case_test ; mixedcaseunquoted --- (0 rows) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High RAM usage on postgres
On Thu, Mar 14, 2013 at 1:55 PM, prashantmalik prashantmal...@gmail.com wrote: Hello, We are facing very HIGH memory utilization on postgreSQL server and need help. Total RAM : 32GB Total CPU : 16cores --- *Table Size:* SELECT relname, pg_size_pretty(relpages::bigint * 8 * 1024) as size, relkind,reltuples::bigint as rows, relpages, relfilenode FROM pg_class ORDER BY relpages DESC; relname |size| relkind | rows | relpages | relfilenode ++-+-+--+- customer | 1863 MB| r | 8307040 | 238507 | 189335 *Query :* SELECT * FROM customer --- top top - 00:14:38 up 44 days, 12:06, 2 users, load average: 3.57, 1.34, 0.69 Tasks: 243 total, 3 running, 240 sleeping, 0 stopped, 0 zombie Cpu(s): 6.5%us, 0.6%sy, 0.0%ni, 92.5%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 32949816k total, 31333260k used, 1616556k free, 526988k buffers Swap: 4192956k total, 1989136k used, 2203820k free, 9182092k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 12671 root 25 0 19.8g 19g 1612 R 100.1 62.6 4:31.78 psql 32546 postgres 15 0 6694m 157m 156m S 0.0 0.5 0:02.91 postmaster --- postgresql.conf shared_buffers = 6400MB# min 128kB # (change requires restart) temp_buffers = 286720# min 800kB work_mem = 320MB# min 64kB maintenance_work_mem = 960MB checkpoint_segments = 32# in logfile segments, min 1, 16MB each checkpoint_timeout = 1h# range 30s-1h checkpoint_completion_target = 0.9# checkpoint target duration, 0.0 - 1.0 checkpoint_warning = 10min# 0 disables effective_cache_size = 16000MB Hello, We are facing very HIGH memory utilization on postgreSQL server and need help. Total RAM : 32GB Total CPU : 16cores --- *Table Size:* SELECT relname, pg_size_pretty(relpages::bigint * 8 * 1024) as size, relkind,reltuples::bigint as rows, relpages, relfilenode FROM pg_class ORDER BY relpages DESC; relname |size| relkind | rows | relpages | relfilenode ++-+-+--+- customer | 1863 MB| r | 8307040 | 238507 | 189335 *Query :* SELECT * FROM customer --- top top - 00:14:38 up 44 days, 12:06, 2 users, load average: 3.57, 1.34, 0.69 Tasks: 243 total, 3 running, 240 sleeping, 0 stopped, 0 zombie Cpu(s): 6.5%us, 0.6%sy, 0.0%ni, 92.5%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 32949816k total, 31333260k used, 1616556k free, 526988k buffers Swap: 4192956k total, 1989136k used, 2203820k free, 9182092k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 12671 root 25 0 19.8g 19g 1612 R 100.1 62.6 4:31.78 psql 32546 postgres 15 0 6694m 157m 156m S 0.0 0.5 0:02.91 postmaster --- More over, the query is throwing all the data on the screen at once without any keyboard interrupt for this table. For all other tables, output is shown in parts when keys are pressed from keyboard. Is a query from another server with less memory(16GB) is made to this postgres, oomkiller kills the postgres thread due to out of memory. what are you intending to do with the 19gb+ data you are querying out? problem is psql buffering whole result set in memory before outputting result. note this is core problem with libpq client library until very recently. there are several easy workarounds: *) use cursor *) don't select entire table, page it out using index (I can suggest some methods ) *) if you are outputting to file, consider using COPY merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
Re: [GENERAL] High RAM usage on postgres
Merlin Moncure wrote: problem is psql buffering whole result set in memory before outputting result. note this is core problem with libpq client library until very recently. there are several easy workarounds: *) use cursor *) don't select entire table, page it out using index (I can suggest some methods ) *) if you are outputting to file, consider using COPY Also there's psql's FETCH_COUNT that is specifically meant to avoid the buffering problem. From the 9.1 manpage: FETCH_COUNT If this variable is set to an integer value 0, the results of SELECT queries are fetched and displayed in groups of that many rows, rather than the default behavior of collecting the entire result set before display. Therefore only a limited amount of memory is used, regardless of the size of the result set. Settings of 100 to 1000 are commonly used when enabling this feature. Keep in mind that when using this feature, a query might fail after having already displayed some rows. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Concurrent updates
I have a table that I want to use as a queue with all functionality (Insert, update, delete) embodied in a stored procedure. Inserts and deletes are no problem. An external program would call the stored procedure to get one or more emails to work on, selecting on state='N', then updating the row so state='P'. My problem is having multiple threads calling the stored procedure simultaneously and getting the same row(s). Selecting FOR UPDATE won't work as, if thread #1 gets 3 rows and thread #2 starts before thread #1 completes (Commits), thread #2 will select the same 3 rows as thread #1 except, since thread #1 will update the state (changing the state to 'P') so that those rows no longer meet thread #2 criteria, and thread #2 will receive zero rows. The table looks like: CREATE TABLE dss.stage_email ( emailid bigserial NOT NULL, email_path text, state character(1) DEFAULT 'N'::bpchar, -- N = New Email, P=Processing, D=Deleting fetch_date timestamp without time zone DEFAULT now(), ingest_date timestamp without time zone ) Steve Erickson Senior Developer 266 East 7th Street, Floor 4 Saint Paul, MN 55101 651.925.3237 office 612.242.1343 cell NOTICE: This email, including any attachments, is covered by the Electronic Communications Privacy Act, is confidential and may be legally privileged. If you are not the intended recipient, you are hereby notified that any retention, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify our office by return email or at our telephone number (651) 925-3200. Then delete and destroy all copies of this email and any attachments. Thank you.
[GENERAL] When did this behavior change (and where else might it bite me)?
In prepping for an upgrade to 9.2.3, I stumbled across this: CREATE TABLE foo ( myint integer, string1 text, string2 text ) WITH ( OIDS=FALSE ); insert into foo values (12345,'Y','N'); select * from foo f where f.myint = 12345 or f.name='Y' In 9.2.3, this returns: ERROR: column f.name does not exist LINE 1: select * from foo f where myint = 12345 or f.name='Y' in 8.4.6 ,this returns no error (and gives me the row from the table) It looks like the parser is short-circuiting in 8.4.6 before stumbling upon the invalid column name - EXCEPT when the column name is NOT a reserved word (although according to http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html, 'name' is not a reserved word). Example - in 8.4.6, this WILL return an error: select * from foo f where f.myint = 12345 or f.poopy='Y' ERROR: column f.poopy does not exist LINE 2: select * from foo f where f.myint = 12345 or f.poopy='Y' ^ NOTE: The problem (assuming the problem is in 8.4.6) only manifests itself when I use table aliases . select * from foo f where myint = 12345 or name='Y' gives an error I would expect: ERROR: column name does not exist LINE 2: select * from foo f where myint = 12345 or name='Y' ^ Any insight into what change (I poured through the release notes and couldn't find anything) may have 'fixed' this behavior so that I might better head these off before my conversion? (yes, my example was contrived - and I did have an bug where the wrong column name was used) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When did this behavior change (and where else might it bite me)?
On Mar 18, 2013, at 9:49 AM, Jeff Amiel becauseimj...@yahoo.com wrote: In prepping for an upgrade to 9.2.3, I stumbled across this: CREATE TABLE foo ( myint integer, string1 text, string2 text ) WITH ( OIDS=FALSE ); insert into foo values (12345,'Y','N'); select * from foo f where f.myint = 12345 or f.name='Y' In 9.2.3, this returns: ERROR: column f.name does not exist LINE 1: select * from foo f where myint = 12345 or f.name='Y' in 8.4.6 ,this returns no error (and gives me the row from the table) That's (unintentionally) an attribute style data type cast - bar.name is the same as name(bar), and tries to cast bar to type name (an internal-use string type) Try select foo from foo, select name(foo::text) from foo and select name(foo) from foo to see what's going on. That was tightened up in 9.1, I think: Casting Disallow function-style and attribute-style data type casts for composite types (Tom Lane) For example, disallow composite_value.text and text(composite_value). Unintentional uses of this syntax have frequently resulted in bug reports; although it was not a bug, it seems better to go back to rejecting such expressions. The CASTand :: syntaxes are still available for use when a cast of an entire composite value is actually intended. 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
Re: [GENERAL] When did this behavior change (and where else might it bite me)?
Jeff Amiel becauseimj...@yahoo.com writes: select * from foo f where f.myint = 12345 or f.name='Y' In 9.2.3, this returns: ERROR: column f.name does not exist LINE 1: select * from foo f where myint = 12345 or f.name='Y' in 8.4.6 ,this returns no error (and gives me the row from the table) That changed in this 9.1 patch: commit 543d22fc7423747afd59fe7214f2ddf6259efc62 Author: Tom Lane t...@sss.pgh.pa.us Date: Sun Nov 7 13:03:19 2010 -0500 Prevent invoking I/O conversion casts via functional/attribute notation. PG 8.4 added a built-in feature for casting pretty much any data type to string types (text, varchar, etc). We allowed this to work in any of the historically-allowed syntaxes: CAST(x AS text), x::text, text(x), or x.text. However, multiple complaints have shown that it's too easy to invoke such casts unintentionally in the latter two styles, particularly field selection. To cure the problem with the narrowest possible change of behavior, disallow use of I/O conversion casts from composite types to string types via functional/attribute syntax. The new functionality is still available via cast syntax. In passing, document the equivalence of functional and attribute syntax in a more visible place. It's not that name is a reserved word or not, it's that it's the name of a datatype that's considered to be of string category; so you can cast just about anything to a name. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] regexp_replace failing on 9.0.4
On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine This is may statement update cms.segment_data s set text = regexp_replace(s.text, '(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') from dm.payload_segment p, support.fix_pathdx_namespace f where s.id = p.segment_data_id and p.item_id = f.item_id and p.content_version = f.maxversion ; UPDATE 76 reported, but zero changes persist When I just select the regexp like so select legacy_id, regexp_replace( substring(s.text, 1, 150) , '(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') from dm.payload_segment p, support.fix_pathdx_namespace f, cms.segment_data s where s.id = p.segment_data_id and p.item_id = f.item_id and p.content_version = f.maxversion ; I get the corrected data (chiefly the 1.6) Can anyone see where I've gone off track? [1]PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.5.0 20100604 [gcc-4_5-branch revision 160292], 64-bit [2]PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.5.0 20100604 [gcc-4_5-branch revision 160292], 64-bit [3]PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] regexp_replace failing on 9.0.4
Rob Sargent robjsarg...@gmail.com writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no changes affecting the regex code between 9.0.3 and 9.0.4. I'm suspicious that your data is different on the different servers. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Addled index
On 17 March 2013 08:30, Tom Lane t...@sss.pgh.pa.us wrote: Oleg Alexeev oalex...@gmail.com writes: * it is varchar columns, 256 and 32 symbols length * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8 * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms, commit_delay = 100, commit_siblings = 10, checkpoint_timeout = 20min, checkpoint_completion_target = 0.7 * postgres 9.2.3 installed via yum repository for version 9.2 * 64 bit Centos 6, installed and updated from yum repository fsync off? Have you had any power failures or other system crashes? ext4 is *way* more prone than ext3 was to corrupt data when fsync is disabled, because it caches and reorders writes much more aggressively. Database located on software md raid 1 based on two SSD disks array. Ext4 filesystem. Database is master node. Meh. I quote from the RHEL6 documentation (Storage Administration Guide, Chapter 20: Solid-State Disk Deployment Guidelines): Red Hat also warns that software RAID levels 1, 4, 5, and 6 are not recommended for use on SSDs. https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/6/html/Storage_Administration_Guide/newmds-ssdtuning.html The part of the docs I'm looking at only asserts that performance is bad, but considering that it's a deprecated combination, it may well be that there are data-loss bugs in there. I'd certainly suggest making sure you are on a *recent* kernel. If that doesn't help, reconsider your filesystem choices. (Disclaimer: I work for Red Hat, but not in the filesystem group, so I don't necessarily know what I'm talking about. But I have the feeling you have chosen a configuration that's pretty bleeding-edge for RHEL6.) regards, tom lane I think fsync=off was really bad idea. -- Oleg V Alexeev E:oalex...@gmail.com
Re: [GENERAL] regexp_replace failing on 9.0.4
On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no changes affecting the regex code between 9.0.3 and 9.0.4. I'm suspicious that your data is different on the different servers. regards, tom lane Good to hear, thought I might have glossed over the telling release note - my usual mo I restored my dev db from prod just for this run. test and prod are out of sync by a couple of weeks. What I had to do ultimately was as follows, create table support.duplicate_pathdx_namespace as select item_id, legacy_id, name, locked_by, maxversion, regexp_replace(substring(content,1,150), '(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') || substring(content, 151) as content from support.fix_pathdx_namespace; and the update finally stuck, then copied that reconstructed textblock over to the target production table in an regexp-less update. Doing the reg_exp_replace on the whole text blog as part of create temp table did not work. I'll double check my dev server from same pre-run dump. Weird. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] regexp_replace failing on 9.0.4
Rob Sargent robjsarg...@gmail.com writes: On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no changes affecting the regex code between 9.0.3 and 9.0.4. I'm suspicious that your data is different on the different servers. Good to hear, thought I might have glossed over the telling release note - my usual mo Maybe we're barking up the wrong tree by suspecting the regex itself. Perhaps the updates were suppressed by a trigger, or the transaction rolled back instead of committing, or some such? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates
On 03/18/2013 02:01 AM, Craig Ringer wrote:
This appears to match Ian's description of having a validation-only cert
list and a separate list of certs used to verify clients. I'd like to
follow Apache's model:
Ready for some more good news?
It's possible that I'm missing something, but Apache (mod_ssl) appears
to exhibit the exact same behavior.
Tested on Fedora 18, with the following packages:
httpd-2.4.3-15.fc18.x86_64
mod_ssl-2.4.3-15.fc18.x86_64
openssl-1.0.1e-3.fc18.x86_64
I have set the following in /etc/httpd/conf.d/ssl.conf:
Listen 3 https
VirtualHost _default_:3
ServerName postgres.example.com
SSLCertificateFile /etc/pki/tls/certs/postgres.crt
SSLCertificateKeyFile /etc/pki/tls/private/postgres.key
SSLCACertificateFile /etc/pki/tls/certs/client-ca.chain
SSLCADNRequestFile /etc/pki/tls/certs/client-ca.crt
SSLVerifyClient require
SSLVerifyDepth 10
Notes:
* The port is set to 3, because that's hard-coded into the
(attached) test client.
* I am using the certificates that I previously sent.
* ServerName is set to postgres.example.com to match its certificate.
* postgres.crt contains its entire chain (postgres.crt + server-ca.crt
+ root-ca.crt), so that I don't have to specify a
SSLCertificateChainFile.
* client-ca.chain is client-ca.crt + root-ca.crt. As with PostgreSQL,
I found that I have to provide the root CA certificate in order for
any client to connect.
With this configuration, the test client is able to connect with the
good client certificate, but it is also able to connect with the bad
client certificate when it presents a certificate chain that includes
the server CA certificate.
--
Ian Pilcher arequip...@gmail.com
Sometimes there's nothing left to do but crash and burn...or die trying.
#include stdlib.h
#include string.h
#include stdio.h
#include sys/types.h
#include sys/socket.h
#include arpa/inet.h
#include openssl/ssl.h
#include openssl/err.h
int main(int argc, char *argv[])
{
SSL_CTX *ctx;
SSL *ssl;
struct sockaddr_in server_addr;
int sock_fd;
if (argc != 3) {
fprintf(stderr, USAGE: %s client_cert client_key\n, argv[0]);
exit(__LINE__);
}
SSL_load_error_strings();
SSL_library_init();
if ((ctx = SSL_CTX_new(SSLv23_client_method())) == NULL) {
ERR_print_errors_fp(stderr);
exit(__LINE__);
}
if (SSL_CTX_use_certificate_chain_file(ctx, argv[1]) != 1) {
ERR_print_errors_fp(stderr);
exit(__LINE__);
}
if (SSL_CTX_use_PrivateKey_file(ctx, argv[2], SSL_FILETYPE_PEM) != 1) {
ERR_print_errors_fp(stderr);
exit(__LINE__);
}
if (SSL_CTX_check_private_key(ctx) != 1) {
ERR_print_errors_fp(stderr);
exit(__LINE__);
}
memset(server_addr, 0, sizeof server_addr);
server_addr.sin_family = AF_INET;
server_addr.sin_port = htons(3);
server_addr.sin_addr.s_addr = htonl(INADDR_LOOPBACK);
if ((sock_fd = socket(AF_INET, SOCK_STREAM, 0)) == -1) {
perror(socket);
exit(__LINE__);
}
if (connect(sock_fd, (struct sockaddr *)server_addr,
sizeof server_addr) == -1) {
perror(connect);
exit(__LINE__);
}
puts(Connected. Starting SSL handshake.);
if ((ssl = SSL_new(ctx)) == NULL) {
ERR_print_errors_fp(stderr);
exit(__LINE__);
}
if (SSL_set_fd(ssl, sock_fd) == 0) {
ERR_print_errors_fp(stderr);
exit(__LINE__);
}
if (SSL_connect(ssl) != 1) {
ERR_print_errors_fp(stderr);
exit(__LINE__);
}
puts(SSL handshake successful. Shutting down.);
return 0;
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] regexp_replace failing on 9.0.4
On 03/18/2013 02:40 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent robjsarg...@gmail.com writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no changes affecting the regex code between 9.0.3 and 9.0.4. I'm suspicious that your data is different on the different servers. Good to hear, thought I might have glossed over the telling release note - my usual mo Maybe we're barking up the wrong tree by suspecting the regex itself. Perhaps the updates were suppressed by a trigger, or the transaction rolled back instead of committing, or some such? regards, tom lane The work was all rolled into a function: o find the chapters; o copy the necessary data (mainly the text blob) into a back-out table o lock the chapters (protect them from exposure to the client app) o perform the regexp_replace as the update to prod. table The function was exec'd in a tx and committed, leaving the back-out table and the programmatic locks in place, but the update itself had been a no-op and continued to be with ad hoc update statements, until I hit the final goofy answer ( rg_replace(string, start) || substring(end) ) Have not yet had a chance to re-create on dev. Test worked like a charm. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Roadmap for Postgres on AIX
Hi guys, What is the road map for Postgres on the AIX platform? I understand that the pg build farm contains an AIX 5.3 server; are there any plans to upgrade to 6.1 and 7.1? Our servers run on AIX and we are evaluating using Postgres as the RDBMS. Part of my evaluation is understanding where the pg team is headed with AIX, and if it is considered a supported platform for the foreseeable future. Any pointers would be appreciated. Thanks, Wasim.
Re: [GENERAL] DB design advice: lots of small tables?
Shaun Thomas stho...@optionshouse.com wrote: On 03/15/2013 08:36 AM, Kevin Grittner wrote: I occasionally hear someone maintaining that having a meaningless sequential ID column as the primary key of each table is required by the relational model. You know, I've heard you mention this a couple times, and I still don't understand why you maintain such a position. Artificial the linkages may be, but unless I'm missing something, the level of abstraction is often necessary to avoid excessive storage requirements and bulky foreign keys. For me, it comes down to two things: 1. Copy a natural arbitrary-length text string possibly millions of times, or use a 32/64-bit integer lookup value. 2. Use multiple natural columns in a primary key, necessitating copying all columns to child tables for foreign keys, or use one surrogate key for both. When I was working for the Wisconsin Courts the Circuit Court databases had some tables which were maintained by a central Court Operations group and some tables holding data generated by each county. The primary key of each county maintained table included a county number (smallint). Most county-maintained tables were related to court cases, and included a court case number as part of the primary key (varchar(14)). Beyond that, other fields were added to each table, and where there was a natural heirarchy to the data the child normally had the PK of its parent and something to make it unique within that set. Often this was a unique number that started at 1 for each parent. Some of these tables, when replicated to the central state-wide site, had hundreds of millions of rows. You would probably be surprised how many queries were able to optimize much better than they could have with a single-column ID in each table. The plans often surprised me, and when I would force the planner to take the plan which seemed obvious to me, 90% of the time the planner had me beat. The other times often suggested new indexes or adjustments to cost factors which benefited the whole workload. Yes, it complicates the schema. That's not the point. But until someone comes up with a database storage method that automatically deduplicates stored data, I can never advocate using arbitrary strings as natural keys, no matter how unique and meaningful they are. If micro-managing disk space usage it the primary concern, single-column synthetic keys are likely to win in many circumstances. I have no doubt that there are cases where it can benefit performance. I can guarantee you that many of the queries we ran at Wisconsin Courts would have been much slower with such synthetic keys because they limit the available plans and force some unnatural navigation. I know because I saw cases where people forced the type of navigation the planner would need to do if synthetic keys were used, and the result was performance orders of magnitude worse. Maybe I just say that because I was burned by just such a column in a previous engagement. It was 64-characters of arbitrary text, and was used as a lookup value for dozens of tables. Had it been mapped to a meaningless surrogate key, several tables would have been halved (or more) in size. Don't even get me started on indexing that horrible monstrosity in every table it lived in. That is getting pretty extreme. Small tables? Yeah, whatever. I don't care. But anything that has the potential to be duplicated millions of times? You better bet that's going to be a serial identifier. Like I said, Wis Cts. has a table that has hundreds of millions of rows and the primary key is a smallint, a varchar(14), and another smallint. You can poke around in it at this site by drilling down on the Court Record Events button for a case: http://wcca.wicourts.gov/ Now, you've been doing this longer than I have, in a wider array of contexts, so you clearly have some perspective I don't. From where I'm sitting though, I don't get the barely suppressed rage. ;) Oh, every time a programmer who had been working in Access or MySQL was hired, I had to have this discussion all over again. You're ripping the scabs off the old wounds from those battles. :-) I usually had to pull out plans from complex (and fast!) queries to make my point. The other thing is that it brings back memories from the early '80s of working in a database product called TOTAL which required explicit navigation over links. Relational technology was such a breath of fresh air compared to that, I dread a pendulum swing back to that. That's been my experience, anyway. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Roadmap for Postgres on AIX
On 3/18/2013 3:39 PM, Wasim Arif wrote: What is the road map for Postgres on the AIX platform? I understand that the pg build farm contains an AIX 5.3 server; are there any plans to upgrade to 6.1 and 7.1? Our servers run on AIX and we are evaluating using Postgres as the RDBMS. Part of my evaluation is understanding where the pg team is headed with AIX, and if it is considered a supported platform for the foreseeable future. I have been building postgres for my companies inhouse use on AIX 6.1 without any issues. I am using IBM XLC to compile it, and I'm building it with plpgsql and plperl support, but not any of the others (we don't use them). I had to compile my own zlib and readline libraries to go with it, which I static link to simplify deployment.because I link it with readline, my builds are GPL tainted and can't be distributed as-is, anyways, my employers policies would not allow me to do that. I use a few tools from the IBM AIX Linux Toolkit for convenience, notably gtar, gmake, and wget. XLC is installed in /usr/vac my build script looks something like... |cd $HOME/src| |wget http:||///zlib-1.2.5.tar.gz| |gtar xzf zlib-||1.2||.||5||.tar.gz| |cd zlib-||1.2||.||5| |export OBJECT_MODE=||64| |CC=/usr/vac/bin/xlc CFLAGS=||-q64 -mcpu=pwr6 -O2| |AR=||/usr/bin/ar| |./configure --prefix=$HOME --||64| |--||static| |gmake clean| |gmake| |gmake install| |cd $HOME/src| |wget http:||///readline-6.1.tar.gz| |gtar xzf readline-||6.1||.tar.gz| |cd readline-||6.1| |export OBJECT_MODE=||64| |CC=/usr/vac/bin/xlc CFLAGS=||-q64| |AR=||/usr/bin/ar| |./configure --prefix=$HOME --||64| |--||static| |gmake clean| |gmake| |gmake install | |cd ~/src| |wget http:||///postgresql-9.1.6.tar.gz| |gtar xzf postgresql-||9.1||.||6||.tar.gz| |cd postgresql-||9.1||.||6| |export OBJECT_MODE=||64| |CC=/usr/vacpp/bin/xlc CFLAGS=||-q64 -I$HOME/src/include -O2 -qarch=pwr5 -qtune=balanced| |\| |||LDFLAGS=||-L$HOME/src/lib| |AR=||/usr/bin/ar| |\| |||./configure --prefix=/opt/pgsql91| |gmake clean| |gmake| |cd contrib| |gmake| |cd ..| |gmake check| |su -c gmake install| |cd /opt| |gtar cvzf /tmp/postgresql-||9.1||.||6||-AIX-||1.0||.tar.gz pgsql91| this tarball is what we use on our production servers, we run postgres from /opt/pgsql91/... -- john r pierce 37N 122W somewhere on the middle of the left coast
[GENERAL] Surge 2013 CFP Open
The Surge 2013 CFP is open. For details or to submit a paper, please visit http://surge.omniti.com/2013 -- Katherine Jeschke Director of Marketing and Creative Services OmniTI Computer Consulting, Inc. 11830 West Market Place, Suite F Fulton, MD 20759 O: 240-646-0770, 222 F: 301-497-2001 C: 443/643-6140 omniti.com Surge 2013 http://surge.omniti.com/2013 The information contained in this electronic message and any attached documents is privileged, confidential, and protected from disclosure. If you are not the intended recipient, note that any review, disclosure, copying, distribution, or use of the contents of this electronic message or any attached documents is prohibited. If you have received this communication in error, please destroy it and notify us immediately by telephone (1-443-325-1360) or by electronic mail (i...@omniti.com). Thank you.
[GENERAL] .backup file documentation
Are is the contents of the .backup file (generated by pg_stop_backup()) documented anywhere? (Some of it is self-explanatory, of course). If not, is there a quick summary of what START WAL LOCATION, STOP WAL LOCATION, and CHECKPOINT LOCATION are? -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enforcing Parameterised Nested Loop Join Order for Foreign Table Joins
Tom,
Thank you for your prompt reply. Your advice has pointed me in the right
direction.
I now have the wrapper identifying columns that are inputs to the web service,
and thus parameterisable. The ec_classes, left_join_clauses and
right_join_clauses trees are scanned for Var exprs that match these attributes.
If they are present, the relid is added to the required list of outer rels for
the path -- this is done as an extension to the logic I posted previously.
In all cases this seems to work, except one. A join between 3 tables. The
foreign table has 2 parameterised columns, each given a restriction based on
one of the other two tables:
adam=# explain select * from l1, l2, foreign1 where foreign1.a = l1.a and
foreign1.b = l2.a;
QUERY PLAN
Merge Join (cost=5000704.96..5001278.44 rows=37822 width=168)
Merge Cond: (l2.a = foreign1.b)
- Sort (cost=85.43..88.50 rows=1230 width=36)
Sort Key: l2.a
- Seq Scan on l2 (cost=0.00..22.30 rows=1230 width=36)
- Sort (cost=5000619.54..5000634.91 rows=6150 width=132)
Sort Key: foreign1.b
- Merge Join (cost=5000135.26..5000232.51 rows=6150 width=132)
Merge Cond: (foreign1.a = l1.a)
- Sort (cost=549.83..552.33 rows=1000 width=96)
Sort Key: foreign1.a
- Foreign Scan on foreign1
(cost=500.00..500.00 rows=1000 width=96)
- Sort (cost=85.43..88.50 rows=1230 width=36)
Sort Key: l1.a
- Seq Scan on l1 (cost=0.00..22.30 rows=1230
width=36)
My path generation logic seems to work:
baserel-cheapest_parameterized_paths = (
{FOREIGNPATH
:pathtype 120
:parent_relids (b 3)
:required_outer (b 1 2)
:rows 500
:startup_cost 0.00
:total_cost 0.00
:pathkeys
:fdw_private
}
{FOREIGNPATH
:pathtype 120
:parent_relids (b 3)
:required_outer (b)
:rows 1000
:startup_cost 500.00
:total_cost 500.00
:pathkeys
:fdw_private
}
)
Yet the planner picks the non-parameterised path:
ForeignPath* best_path = {FOREIGNPATH
:pathtype 120
:parent_relids (b 3)
:required_outer (b)
:rows 1000
:startup_cost 500.00
:total_cost 500.00
:pathkeys
:fdw_private
}
I’ve tried adjusting planner tuneables to disable all join types except nested
loop, and setting `join_collapse_limit` to 1 with no desirable outcome.
Yet, adding a restriction clause between the other two tables forces them to be
scanned first:
adam=# explain select * from l1, l2, foreign1 where foreign1.a = l1.a and
foreign1.b = l2.a and l1.b l2.b;
QUERY PLAN
-
Nested Loop (cost=0.00..2544241.17 rows=12608 width=168)
- Nested Loop (cost=0.00..22741.17 rows=504300 width=72)
Join Filter: (l1.b l2.b)
- Seq Scan on l1 (cost=0.00..22.30 rows=1230 width=36)
- Materialize (cost=0.00..28.45 rows=1230 width=36)
- Seq Scan on l2 (cost=0.00..22.30 rows=1230 width=36)
- Foreign Scan on foreign1 (cost=0.00..0.00 rows=500 width=96)
Filter: ((a = l1.a) AND (b = l2.a))
ForeignPath* best_path = {FOREIGNPATH
:pathtype 120
:parent_relids (b 3)
:required_outer (b 1 2)
:rows 500
:startup_cost 0.00
:total_cost 0.00
:pathkeys
:fdw_private
}
On 18/03/2013, at 4:09 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Adam Zegelin a...@relational.io writes:
Some service endpoints have no concept of unqualified queries. In the
example above, a ‘sequence scan’ of Bing is a not possible.
In that case, you shouldn't be generating such a path. But keep in mind
that this may lead to failure to produce any plan at all for some
queries. If the foreign data source is really so broken that it can't
do that, then you have little choice ... but you shouldn't be thinking
of that as anything but a broken design decision on their part.
I tried adding a condition that would prevent the non-parameterised path from
being generated if the service only supported parameterised scans. Postgres
refuses to generate a plan: ERROR: could not devise a query plan for the
given query. I did a bit of digging and this error is generated by
pathnode.c:set_cheapest . As there is no non-parameterised
`cheapest_total_path` the error is raised (line 253).
For now, I just add an expensive non-pramerterised path and let the FDW throw
an error if no qual is found involving the required columns.
Regards,
Adam
smime.p7s
Description: S/MIME cryptographic signature
Re: [GENERAL] Roadmap for Postgres on AIX
Wasim Arif wasima...@gmail.com writes: What is the road map for Postgres on the AIX platform? I understand that the pg build farm contains an AIX 5.3 server; are there any plans to upgrade to 6.1 and 7.1? The reason there's an AIX 5.3 buildfarm member is that someone cares enough about preserving portability to that platform to provide a buildfarm member. If you're worried about other AIX releases, I suggest you do likewise. It's not a huge burden. Basic info about it is here: http://buildfarm.postgresql.org/cgi-bin/register-form.pl By and large, our approach to AIX is the same as to any other platform: we'll support it as long as users of the platform provide testing and any necessary portability fixes. That burden might fall more directly on you, as one of a relatively small number of users, than it would on somebody using say Linux or BSD. But as long as you are willing to do some of the work you need not fear that we're planning to abandon you. (FWIW, I believe there was a more-recent AIX version in the buildfarm till recently. Don't know why its owner stopped running it.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enforcing Parameterised Nested Loop Join Order for Foreign Table Joins
Adam Zegelin a...@relational.io writes:
My path generation logic seems to work:
baserel-cheapest_parameterized_paths = (
{FOREIGNPATH
:pathtype 120
:parent_relids (b 3)
:required_outer (b 1 2)
:rows 500
:startup_cost 0.00
:total_cost 0.00
:pathkeys
:fdw_private
}
{FOREIGNPATH
:pathtype 120
:parent_relids (b 3)
:required_outer (b)
:rows 1000
:startup_cost 500.00
:total_cost 500.00
:pathkeys
:fdw_private
}
)
I think you missed my point: you should not be insisting on a maximal
set of required outer rels.
In this particular case, it won't generate a cross-product join of l1
and l2 because there's a heuristic that says that's unlikely to be a
good idea. But in related cases, there could be join order restrictions
that *require* us not to do the joins in that order; so even if you
could talk us out of applying that heuristic, this code is still subject
to undesirable failures. You really need to provide three paths using
the three possible combinations of outer rels.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Analyzing the types of prepared statements
On Mar 16, 2013, at 8:30 AM, Jason Dusek jason.du...@gmail.com wrote: However, it is not clear to me at this juncture how to get the return type for a statement, given its text. Preparing and looking it up in pg_prepared_statements will retrieve the argument types but not the return type. Wrapping the query in a SQL stored procedure which returns record doesn't cause the server to infer and store a more specific type. You might want to take a look at the extended query protocol here: http://www.postgresql.org/docs/current/interactive/protocol-flow.html If you send a parse (P) messaged followed by describe (D), you can get a row description for the return type without executing anything. John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] .backup file documentation
On Mar 18, 2013, at 7:36 PM, Christophe Pettus wrote: Are is the contents of the .backup file (generated by pg_stop_backup()) documented anywhere? (Some of it is self-explanatory, of course). If not, is there a quick summary of what START WAL LOCATION, STOP WAL LOCATION, and CHECKPOINT LOCATION are? http://www.postgresql.org/docs/9.2/static/functions-admin.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] regexp_replace failing on 9.0.4
Maybe we're barking up the wrong tree by suspecting the regex itself. Perhaps the updates were suppressed by a trigger, or the transaction rolled back instead of committing, or some such? regards, tom lane Barking mad, more like it. I had rolled back the execution of my function in my dev env. and running again produces the same result as experienced in production system. Why the update of the text type field view regexp_replace is failing still confuses me, but that's pretty much my natural state. The size of the text field ranges from 7.5k to 24k char. Here is the update (again) update cms.segment_data s set text = regexp_replace(f.content, '(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') from dm.payload_segment p, support.fix_pathdx_namespace f where s.id = p.segment_data_id and p.item_id = f.item_id and p.content_version = f.maxversion ; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/18/2013 08:55 PM, Stephen Frost wrote: Makes sense to me. I'm not particular about the names, but isn't this set of CAs generally considered intermediary? Eg: 'trusted', ' intermediate', etc? They are intermediary, but we're dealing with the case where trust and authorization are not the same thing. Trust stems from the trusted root in the SSL CA model, but that's a chain of trust for *identity* (authentication), not *authorization*. Bob J. Criminal might well have a client certificate from a trusted authority proving that he's who he says he is (he's authenticated) but we sure as hell don't want to authorize his access to anything. That's where the intermediate certs come in. We might say Only users with certificates issued by our corporate HR team are authorized to connect to our servers. This is a root of trust, but this time it's a root of trust to *authorize*, not just to authenticate. The usual SSL terminology doesn't consider this, because it's a simple back and white trust model where authenticated = authorized. I guess that suggests we should be calling this something like 'ssl_authorized_client_roots'. - -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.13 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJRR/dqAAoJELBXNkqjr+S2TV4H/3f9Hnf9JhSuGhWblh2adgTJ Rkdx/9RbByJDMJP0s0c8C1sXaWZGJmKmLhJoes4IIvOVW85SVUa9WoT+UBJPdx9P esUNsSLFokLqom3TxNRZOHaloyZ+OZafSUnKCwMOIvD0hIehrS3Wcg70QMSj06tX h22BVhA8bzO1Wdg9UdD98jcuWdEbLgWzVtvIXjICcMJ1azgiF1VY4zwUUbBJBfLG UIA7+2TtVaXQuge6qWgId0RTKKrb6cLHXCSQ/rigy0mRH9m/G5jKmqENvLAnafI4 4lSBPyDzNj2fBfP9YgIiAe/EGjnJMWQfBBghQI3QrK2kjOZXtzZoOb4XEjfn3FI= =u+2j -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates
Craig, * Craig Ringer (cr...@2ndquadrant.com) wrote: They are intermediary, but we're dealing with the case where trust and authorization are not the same thing. Trust stems from the trusted root in the SSL CA model, but that's a chain of trust for *identity* (authentication), not *authorization*. Oh, I see. The usual SSL terminology doesn't consider this, because it's a simple back and white trust model where authenticated = authorized. That's not entirely accurate on a couple of levels. First, basic SSL is only concerned with authentication, authorization has historically been left up to the application. The more typical approach with the situation you're describing is to have an organization-level root CA which you only issue certs against. If you're using a public CA as your root, then you need to make sure you know how to ensure only the right people have access, typically be storing in the mapping table the unique ID issued by the CA for your users. It's very rare, from what I've seen, for public CAs to issue intermediate CAs to organizations to generate their own certs off of, so I'm a bit confused about how we got to this point. What I *have* seen is cross-root-cert trusts (known as the Federal Bridge in the US government), but that's quite a different thing as you have multiple self-signed root CAs involved and need to know how to properly traverse between them based on the trusts which have been built. Regarding cross-CAS authorization, there are extended attributes which are listed in the certificates that applications are expected to look at when considering authorization for the client. It's been taken further than that however, where inter-CA trusts have been defined with actual mappings between these extended attributes, including 'null' mappings (indicating that CA 'A' doesn't trust attribute 'q' from CA 'B'). I guess that suggests we should be calling this something like 'ssl_authorized_client_roots'. I'm no longer convinced that this really makes sense and I'm a bit worried about the simple authentication issue which I thought was at the heart of this concern. Is there anything there that you see as being an issue with what we're doing currently..? I do think we want to figure out a way to improve our mapping table to be able to use more than just the CN, since that can be repeated in multiple certs issued from a root CA, particularly when there are intermediary CAs. One option might be to provide a way to map against a specific issuing CA, or to a CA in the chain, but there's a lot of risk to that due to CA churn (in large setups, you're going to have lots of users who have certs issued from a bunch of different CAs, and those user certs will roll to new CAs as new badges are issued, for example..). It can get to be a real nightmare to try and keep up with all of the changes at that level. Thanks, Stephen signature.asc Description: Digital signature
