[BUGS] BUG #5306: psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC
The following bug has been logged online: Bug reference: 5306 Logged by: Email address: christophe.mou...@progimed.fr PostgreSQL version: 8.4.1 Operating system: Linux/Redhat Description:psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC Details: I made an installation of Postgres on a redhat machine after compiling sources on a redhat machine. Everything is OK. When i install Postgres on a debian machine, i get an error when using psql : "psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC" If i compile sources on debian-machine, psql does not work on a redhat machine. Same error message. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5306: psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC
"" writes: > I made an installation of Postgres on a redhat machine after compiling > sources on a redhat machine. Everything is OK. > When i install Postgres on a debian machine, i get an error when using psql > : "psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: > BC" > If i compile sources on debian-machine, psql does not work on a redhat > machine. Same error message. This is not a bug. They're two different platforms and you shouldn't expect compiled executables to be portable between them. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5306: psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC
On Tue, Feb 2, 2010 at 7:38 AM, Tom Lane wrote: > "" writes: >> I made an installation of Postgres on a redhat machine after compiling >> sources on a redhat machine. Everything is OK. >> When i install Postgres on a debian machine, i get an error when using psql >> : "psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: >> BC" > >> If i compile sources on debian-machine, psql does not work on a redhat >> machine. Same error message. > > This is not a bug. They're two different platforms and you shouldn't > expect compiled executables to be portable between them. > Just as a note (and clarification): Sometimes (very occasionally) I have had to move existing Pg binaries across machines (and even different versions of the same Linux Distro: Fedora). Occasionally I have done this when I have to make absolutely certain that file-level backups restore on a different machine. However, it is not a common task. Very often when I have done this, I have found that I also have to copy over the Readline .so files from the original machine. First, this isn't a PostgreSQL problem, and it certainly isn't a Pg bug. It has to do with changes to the Readline library and the way linking works on Linux systems. In general, the word of advice is that unless you know what you are doing, don't try this at home. Best Wishes, Chris Travers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5307: Crash in AllocSetAlloc
The following bug has been logged online: Bug reference: 5307 Logged by: Igor Lasic Email address: i...@a-bb.net PostgreSQL version: 8.3.7 Operating system: Linux Description:Crash in AllocSetAlloc Details: We have an application running C-API stored procedures. After running for a while postgres crashes with: If I increase shared memory application runs longer but eventually crashes. - 2010-02-01 10:56:08.689 UTC LOG: server process (PID 12925) was terminated by signal 11: Segmentation fault 2010-02-01 10:56:08.689 UTC LOG: terminating any other active server processes ipdr ipdr 2010-02-01 10:56:08.689 UTC WARNING: terminating connection because of crash of another server process ipdr ipdr 2010-02-01 10:56:08.689 UTC DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, b\ ecause another server process exited abnormally and possibly corrupted shared memory. (gdb) where #0 AllocSetAlloc (context=0xb5dd1b0, size=703) at aset.c:747 #1 0x004aaed2 in scanner_init ( str=0xb6038b0 "SELECT NULL::int2 AS cnn_servicedirection, NULL::inet AS cnn_cmtsipaddress, NULL::oid AS cnn_cmtsmdifindex, NULL::varcha\ r AS cnn_serviceclassname, NULL::macaddr AS cnn_cmmacaddress, NULL::int4 AS cnn"...) at scan.l:829 #2 0x004bc268 in raw_parser (str=0xfaf60a53212c42ed ) at parser.c:51 #3 0x005ab435 in pg_parse_query ( query_string=0xb6038b0 "SELECT NULL::int2 AS cnn_servicedirection, NULL::inet AS cnn_cmtsipaddress, NULL::oid AS cnn_cmtsmdifindex, NUL\ L::varchar AS cnn_serviceclassname, NULL::macaddr AS cnn_cmmacaddress, NULL::int4 AS cnn"...) at postgres.c:554 #4 0x00530977 in _SPI_prepare_plan (src=0xfaf60a53212c42ed , plan=0x7) at spi.c:1475 #5 0x005324f0 in SPI_execute ( src=0xb6038b0 "SELECT NULL::int2 AS cnn_servicedirection, NULL::inet AS cnn_cmtsipaddress, NULL::oid AS cnn_cmtsmdifindex, NULL::varcha\ r AS cnn_serviceclassname, NULL::macaddr AS cnn_cmmacaddress, NULL::int4 AS cnn"..., read_only=1 '\001', tcount=1) at spi.c:340 #6 0x2b1fc13b0e0b in ?? () #7 0x0064 in ?? () -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5307: Crash in AllocSetAlloc
Igor Lasic wrote: > > The following bug has been logged online: > > Bug reference: 5307 > Logged by: Igor Lasic > Email address: i...@a-bb.net > PostgreSQL version: 8.3.7 > Operating system: Linux > Description:Crash in AllocSetAlloc > Details: > > We have an application running C-API stored procedures. Let's see the code. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5307: Crash in AllocSetAlloc
"Igor Lasic" writes: > We have an application running C-API stored procedures. After running for a > while postgres crashes with: > If I increase shared memory application runs longer but eventually crashes. The odds are incredibly high that this means there's a memory-stomping bug in your C stored procedures. You might try testing them in a build with --enable-debug + --enable-cassert, which will enable some sanity checking code in the memory context support. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] REFERENCES misbehaves with inheritance
On Sun, Jan 31, 2010 at 9:07 AM, Steve White wrote:
> Hi,
>
> I'm aware that this is a manifestation of the problem mentioned in the
> Caveats subsection of the Inheritance section. I want to emphasize it,
> and maybe rattle your cage a bit.
>
> I find the Postgres notion of inheritance very compelling. Conceptually
> it does what I want, when I create tables of related, but different kinds
> of things.
>
> Unfortunately these little ommissions really foul up implementations
> using inheritance.
>
> For instance: a field that REFERENCES a field in an inherited table is
> unaware that records have been added to the inherited table, by way of
> records being added to inheriting tables.
>
> This is awful. One is forced to make choices between various evils.
>
> EXAMPLE:
>
>
> CREATE TABLE a (
> a_id SERIAL PRIMARY KEY
> );
>
> CREATE TABLE a1 (
> ) INHERITS( a );
>
> CREATE TABLE a2 (
> ) INHERITS( a );
>
> CREATE TABLE b (
> b_id SERIAL PRIMARY KEY,
> a_id INTEGER,
> FOREIGN KEY (a_id) REFERENCES a(a_id)
> );
> -- ---
>
> INSERT INTO a1 VALUES( DEFAULT );
>
> -- The following results in a foreign key violation, saying
> -- no row with a_id=1 is present in table "a":
> INSERT INTO b VALUES( DEFAULT, CURRVAL('a_a_id_seq') );
>
> -- However this indicates that table "a" has a row with a_id=1:
> SELECT * FROM a;
I am guessing that the problem with this feature is not so much that
it's hard to implement as that the performance could be terrible: no
one has gotten around to adding the ability to create an index that
includes both the parent and all of its inheritance children.
I suppose in theory if each child had an index on the relevant
column(s) it might not be too bad, for certain use cases, but if you
have, say, a thousand child tables and have to make an index probe
into each one for each row inserted into the referring table, that
could be pretty ugly (~2k random seeks per row - ouch).
...Robert
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Warnings building 8.5alpha3 on Solaris 10
I get the following warnings building 8.5alpha3 on Solaris 10. I do not get these warnings building 8.4 exactly the same way. standby.c:239: warning: int format, pid_t arg (arg 3) standby.c:247: warning: int format, pid_t arg (arg 5) descriptor.c:23: warning: missing braces around initializer descriptor.c:23: warning: (near initialization for `descriptor_once.__pthread_once_pad') memory.c:73: warning: missing braces around initializer memory.c:73: warning: (near initialization for `auto_mem_once.__pthread_once_pad') connect.c:16: warning: missing braces around initializer connect.c:16: warning: (near initialization for `actual_connection_key_once.__pthread_once_pad') misc.c:61: warning: missing braces around initializer misc.c:61: warning: (near initialization for `sqlca_key_once.__pthread_once_pad') (I'm leaving out the known "unused variable `yyg'" issue in scan.c) Also, I get the following on both 8.4 and 8.5 (same builds as above): auth.c:82: warning: initialization from incompatible pointer type (except in 8.4 it's at line 80) I get none of these warnings (except the "yyg" one) building 8.5alpha3 on my Mac (OS X 10.5.8). The closest thing I can find to a mention of this issue is: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01935.php Isaac Morland CSCF Web Guru DC 2554C, x36650WWW Software Specialist -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] whole-row functional index?
jasen=# update member set id=id where id=441; UPDATE 1 jasen=# create index member_search on member using gin ( (get_search_text_internal(member)) ); CREATE INDEX jasen=# update member set id=id where id=441; ERROR: table row type and query-specified row type do not match DETAIL: Physical storage mismatch on dropped attribute at ordinal position 109. jasen=# select version(); -[ RECORD 1 ]- version | PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2 the function get_text_search_internal returns type tsvector -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Warnings building 8.5alpha3 on Solaris 10
Isaac Morland writes: > I get the following warnings building 8.5alpha3 on Solaris 10. > I do not get these warnings building 8.4 exactly the same way. > standby.c:239: warning: int format, pid_t arg (arg 3) > standby.c:247: warning: int format, pid_t arg (arg 5) That looks like a real problem, but it seems to be cleaned up already in HEAD. > descriptor.c:23: warning: missing braces around initializer > descriptor.c:23: warning: (near initialization for > `descriptor_once.__pthread_once_pad') > memory.c:73: warning: missing braces around initializer > memory.c:73: warning: (near initialization for > `auto_mem_once.__pthread_once_pad') > connect.c:16: warning: missing braces around initializer > connect.c:16: warning: (near initialization for > `actual_connection_key_once.__pthread_once_pad') > misc.c:61: warning: missing braces around initializer > misc.c:61: warning: (near initialization for > `sqlca_key_once.__pthread_once_pad') These all seem to relate to uses of PTHREAD_ONCE_INIT, which is a system-provided macro --- so I think you need to complain to Sun that their headers provide warning-inducing declarations. It should be just cosmetic as far as functionality goes, though. > auth.c:82: warning: initialization from incompatible pointer type > (except in 8.4 it's at line 80) This one probably is because of a discrepancy in "const" decorations of parameters for the PAM callback proc. Again, it's cosmetic, and seems more likely to indicate obsolete Solaris headers than anything we should change on our end. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] whole-row functional index?
Jasen Betts writes: > jasen=# update member set id=id where id=441; > UPDATE 1 > jasen=# create index member_search on member using gin ( > (get_search_text_internal(member)) ); > CREATE INDEX > jasen=# update member set id=id where id=441; > ERROR: table row type and query-specified row type do not match > DETAIL: Physical storage mismatch on dropped attribute at ordinal > position 109. http://archives.postgresql.org/pgsql-bugs/2010-01/msg00089.php This is fixed for the next 8.4 update, but it's not going to be fixed in 8.3.x. Suggest recreating the table without any dropped columns. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On 02/01/2010 08:06 AM, Tom Lane wrote:
> Heikki Linnakangas writes:
>> Fujii Masao wrote:
>>> In HEAD, psql using conninfo fails in connecting to the server as follows.
>>>
>>> $ bin/psql "host=localhost"
>>> psql: FATAL: database "host=localhost" does not exist
>>>
>>> This is because the recently-introduced PQconnectStartParams()
>>> doesn't handle correctly the dbname parameter containing '='.
>
>> Hmm, I don't think that was ever really supposed to work, it was
>> accidental that it did.
>
> No, it was intentional.
Here's a patch.
If "=" is found in the dbname psql argument, the argument is assumed to
be a conninfo string. In that case, append application_name to the
conninfo and use PQsetdbLogin() as before. Otherwise use the new
PQconnectdbParams().
Also only uses static assignments for array constructors.
Objections?
Thanks,
Joe
Index: src/bin/psql/startup.c
===
RCS file: /opt/src/cvs/pgsql/src/bin/psql/startup.c,v
retrieving revision 1.159
diff -c -r1.159 startup.c
*** src/bin/psql/startup.c 28 Jan 2010 06:28:26 - 1.159
--- src/bin/psql/startup.c 3 Feb 2010 00:53:08 -
***
*** 90,97
char *password = NULL;
char *password_prompt = NULL;
bool new_pass;
- const char *keywords[] = {"host","port","dbname","user",
- "password","application_name",NULL};
set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("psql"));
--- 90,95
***
*** 173,192
/* loop until we have a password if requested by backend */
do
{
! const char *values[] = {
! options.host,
! options.port,
! (options.action == ACT_LIST_DB &&
!options.dbname == NULL) ? "postgres" : options.dbname,
! options.username,
! password,
! pset.progname,
! NULL
! };
!
! new_pass = false;
! pset.db = PQconnectdbParams(keywords, values);
if (PQstatus(pset.db) == CONNECTION_BAD &&
PQconnectionNeedsPassword(pset.db) &&
--- 171,218
/* loop until we have a password if requested by backend */
do
{
! /*
! * If the dbName parameter contains '=', assume it's a conninfo string.
! */
! if (options.dbname && strchr(options.dbname, '='))
! {
! PQExpBuffer dbName = createPQExpBuffer();
!
! appendPQExpBuffer(dbName, "%s application_name=%s",
! options.dbname, pset.progname);
! new_pass = false;
! pset.db = PQsetdbLogin(options.host, options.port, NULL, NULL,
! dbName->data, options.username, password);
! destroyPQExpBuffer(dbName);
! }
! else
! {
! const char *keywords[] = {
! "host",
! "port",
! "dbname",
! "user",
! "password",
! "application_name",
! NULL
! };
! const char **values = pg_malloc(sizeof(keywords));
!
! values[0] = options.host;
! values[1] = options.port;
! values[2] = (options.action == ACT_LIST_DB &&
! options.dbname == NULL) ?
! "postgres" : options.dbname;
! values[3] = options.username;
! values[4] = password;
! values[5] = pset.progname;
! values[6] = NULL;
!
! new_pass = false;
! pset.db = PQconnectdbParams(keywords, values);
! free(values);
! }
if (PQstatus(pset.db) == CONNECTION_BAD &&
PQconnectionNeedsPassword(pset.db) &&
signature.asc
Description: OpenPGP digital signature
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On Wed, Feb 3, 2010 at 10:05 AM, Joe Conway wrote: > Here's a patch. Thanks! > If "=" is found in the dbname psql argument, the argument is assumed to > be a conninfo string. In that case, append application_name to the > conninfo and use PQsetdbLogin() as before. Otherwise use the new > PQconnectdbParams(). > > Also only uses static assignments for array constructors. > > Objections? I think that PQconnectdbParams() rather than psql should handle the dbname containing "=". Otherwise whenever we use PQconnectdbParams(), we would have to check for the content of the dbname before calling it in the future application. Which looks very messy for me. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On 02/02/2010 05:46 PM, Fujii Masao wrote: > On Wed, Feb 3, 2010 at 10:05 AM, Joe Conway wrote: >> Objections? > > I think that PQconnectdbParams() rather than psql should handle the > dbname containing "=". Otherwise whenever we use PQconnectdbParams(), > we would have to check for the content of the dbname before calling > it in the future application. Which looks very messy for me. But I thought the whole point of PQconnectdbParams() was to provide an extensible way to accept parameters when they are already parsed? It doesn't make any sense to me to have conninfo parsing capability built into PQconnectdbParams(). For that matter it's kind of an ugly hack that PQsetdbLogin() supports it, IMHO. Joe signature.asc Description: OpenPGP digital signature
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
Joe Conway writes: > If "=" is found in the dbname psql argument, the argument is assumed to > be a conninfo string. In that case, append application_name to the > conninfo and use PQsetdbLogin() as before. Otherwise use the new > PQconnectdbParams(). This seems bogus on a couple of levels. First off, I thought the idea was to get away from using PQsetdbLogin at all. If we go down this path we'll never be rid of it. Second, to preserve backwards compatibility we will have to duplicate this same type of logic in any of the other places we want to replace PQsetdbLogin (because it's actually PQsetdbLogin that implements the dbname-containing-equal-sign special case in prior releases). I count nine remaining calls of that function between bin/ and contrib/, at least some of which were supposed to get application_name-ified before release. While I'm looking at this, there's another bogosity in the original patch: it neglected the PQsetdbLogin call in psql/command.c, meaning that doing \c would result in losing the application_name setting. I'm not entirely sure about a better way to do it, but this approach seems rather unsatisfactory. > Also only uses static assignments for array constructors. Uh, no, you're still depending on a non-static constructor for the keywords[] array. I'm not at all certain whether my portability concern is still valid in 2010, but if it is, this doesn't fix it. This doesn't do very much to help with the maintenance risk about keeping the two arrays in step, either --- now there's neither a direct nor a visual matchup between the entries. I'd suggest something like keywords[0] = "host"; values[0] = options.host; keywords[1] = "port"; values[1] = options.port; ... regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On 02/02/2010 05:59 PM, Tom Lane wrote: > This seems bogus on a couple of levels. First off, I thought the idea > was to get away from using PQsetdbLogin at all. If we go down this path > we'll never be rid of it. Second, to preserve backwards compatibility > we will have to duplicate this same type of logic in any of the other > places we want to replace PQsetdbLogin (because it's actually > PQsetdbLogin that implements the dbname-containing-equal-sign special > case in prior releases). I count nine remaining calls of that function > between bin/ and contrib/, at least some of which were supposed to get > application_name-ified before release. > While I'm looking at this, there's another bogosity in the original > patch: it neglected the PQsetdbLogin call in psql/command.c, meaning > that doing \c would result in losing the application_name setting. OK, based on this and the similar complaint fro Fujii-san, I guess I'll have another go at it. I didn't understand that this patch was leading to replacement of PQsetdbLogin() entirely -- should I go ahead and eliminate use of PQsetdbLogin() in our source tree now? > concern is still valid in 2010, but if it is, this doesn't fix it. > This doesn't do very much to help with the maintenance risk about > keeping the two arrays in step, either --- now there's neither a > direct nor a visual matchup between the entries. I'd suggest > something like > > keywords[0] = "host"; > values[0] = options.host; > keywords[1] = "port"; > values[1] = options.port; Will do. Joe signature.asc Description: OpenPGP digital signature
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
Fujii Masao writes: > On Wed, Feb 3, 2010 at 10:05 AM, Joe Conway wrote: >> Objections? > I think that PQconnectdbParams() rather than psql should handle the > dbname containing "=". Otherwise whenever we use PQconnectdbParams(), > we would have to check for the content of the dbname before calling > it in the future application. Which looks very messy for me. Yeah, I just complained about the same thing. However I don't think we should make PQconnectdbParams do that unconditionally. In a lot of applications, it is a key advantage of PQconnectdbParams that there's no possibility of funny characters in the arguments resulting in "SQL injection", ie, somebody being able to set connection parameters they weren't supposed to. Even without any malicious intent, having to think about quoting and so forth destroys a lot of the value. Since we haven't yet released PQconnectdbParams, it's not too late to twiddle its API. What I'm thinking about is an additional boolean parameter "expand_dbname", which only if true would enable treating an equal-sign-containing dbname like a conninfo string. Passing true would be okay for command-line apps where the user is supposed to control all the conn parameters anyway, but apps that want more security would pass false. We should also give more than zero thought to how values coming from the expanded dbname should interact with values from other arguments to PQconnectdbParams --- which should override which? And should there be an order dependency? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On 02/02/2010 06:10 PM, Tom Lane wrote: > Since we haven't yet released PQconnectdbParams, it's not too late > to twiddle its API. What I'm thinking about is an additional > boolean parameter "expand_dbname", which only if true would enable > treating an equal-sign-containing dbname like a conninfo string. > Passing true would be okay for command-line apps where the user is > supposed to control all the conn parameters anyway, but apps that > want more security would pass false. OK > We should also give more than zero thought to how values coming from the > expanded dbname should interact with values from other arguments to > PQconnectdbParams --- which should override which? And should there be > an order dependency? My first thought was to duplicate the logic used by PQsetdbLogin(). It uses the conninfo string, fills in the defaults using connectOptions1(), applies the supplied other arguments overriding the defaults, and then finally computes derived options with connectOptions2(). It is essentially the same as PQconnectdb() except the supplied parameters are used before setting the derived options. Joe signature.asc Description: OpenPGP digital signature
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
Joe Conway writes: > On 02/02/2010 06:10 PM, Tom Lane wrote: >> We should also give more than zero thought to how values coming from the >> expanded dbname should interact with values from other arguments to >> PQconnectdbParams --- which should override which? And should there be >> an order dependency? > My first thought was to duplicate the logic used by PQsetdbLogin(). It > uses the conninfo string, fills in the defaults using connectOptions1(), > applies the supplied other arguments overriding the defaults, and then > finally computes derived options with connectOptions2(). It is > essentially the same as PQconnectdb() except the supplied parameters are > used before setting the derived options. The difference with PQconnectdbParams is that the dbname might not be the first thing in the parameter array. I think that a straightforward implementation would have the effect of the expanded dbname overriding parameters given before it, but not those given after it. Consider keyword[0] = "port"; values[0] = "5678"; keyword[1] = "dbname"; values[1] = "dbname = db user = foo port = "; keyword[2] = "user"; values[2] = "uu"; What I'm imagining is that this would end up equivalent to dbname = db user = uu port = . That's probably reasonable, and maybe even useful, as long as it's documented. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5308: How to disable Case sensitivity on naming identifiers
The following bug has been logged online: Bug reference: 5308 Logged by: Kelly SACAULT Email address: kelly.saca...@gmail.com PostgreSQL version: 8.4.2 Operating system: Ubuntu 9.10 Description:How to disable Case sensitivity on naming identifiers Details: I have installed Postgresql using Ubuntu Synaptic. In the contrary of what is stated in the official manual, I have to write case sensitive SQL statements in my postgresql connexion. What parameter do I have to change in the postgresaql configuration ? I have spent many hours in studying the parameters, the faqs and the forums. I have found nothing to make my SQL statements case-insensitive. I want to be able to execute successfully such stmts: SELECT col1 FROM myTABLE SELECT Col1 FROM myTable please, may you help ? Kelly -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5308: How to disable Case sensitivity on naming identifiers
On Tue, Feb 2, 2010 at 12:11 PM, Kelly SACAULT wrote: > > The following bug has been logged online: > > Bug reference: 5308 > Logged by: Kelly SACAULT > Email address: kelly.saca...@gmail.com > PostgreSQL version: 8.4.2 > Operating system: Ubuntu 9.10 > Description: How to disable Case sensitivity on naming identifiers > Details: > > I have installed Postgresql using Ubuntu Synaptic. > > In the contrary of what is stated in the official manual, I have to write > case sensitive SQL statements in my postgresql connexion. > > What parameter do I have to change in the postgresaql configuration ? I have > spent many hours in studying the parameters, the faqs and the forums. I have > found nothing to make my SQL statements case-insensitive. > I want to be able to execute successfully such stmts: > > SELECT col1 FROM myTABLE > > SELECT Col1 FROM myTable > > please, may you help ? I thought PgSQL was case insensitive by default and that both those would be executed as: SELECT col1 FROM mytable; If you are seeing otherwise in the manual, can you provide a section? Best Wishes, Chris Travers > > Kelly > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5308: How to disable Case sensitivity on naming identifiers
Hello, usually sql identifiers are case insensitive. There are exception. If you use double quotes for sql identifier, then you have to write exact same identifier everywhere. postgres=# create table Foo(a integer); CREATE TABLE Time: 174,078 ms postgres=# select * from Foo; a --- (0 rows) Time: 33,255 ms postgres=# select * from foo; a --- (0 rows) Time: 0,822 ms postgres=# drop table foo; DROP TABLE Time: 34,945 ms postgres=# create table "Foo"(a integer); CREATE TABLE Time: 3,225 ms postgres=# select * from foo; ERROR: relation "foo" does not exist LINE 1: select * from foo; ^ postgres=# select * from Foo; ERROR: relation "foo" does not exist LINE 1: select * from Foo; ^ postgres=# select * from "Foo"; a --- (0 rows) Time: 1,277 ms you cannot change this behave. Just don't use double quotes in create statement. Regards Pavel Stehule 2010/2/2 Kelly SACAULT : > > The following bug has been logged online: > > Bug reference: 5308 > Logged by: Kelly SACAULT > Email address: kelly.saca...@gmail.com > PostgreSQL version: 8.4.2 > Operating system: Ubuntu 9.10 > Description: How to disable Case sensitivity on naming identifiers > Details: > > I have installed Postgresql using Ubuntu Synaptic. > > In the contrary of what is stated in the official manual, I have to write > case sensitive SQL statements in my postgresql connexion. > > What parameter do I have to change in the postgresaql configuration ? I have > spent many hours in studying the parameters, the faqs and the forums. I have > found nothing to make my SQL statements case-insensitive. > I want to be able to execute successfully such stmts: > > SELECT col1 FROM myTABLE > > SELECT Col1 FROM myTable > > please, may you help ? > > Kelly > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] whole-row functional index?
On 2010-02-02, Tom Lane wrote: > Jasen Betts writes: >> jasen=# update member set id=id where id=441; >> UPDATE 1 >> jasen=# create index member_search on member using gin ( >> (get_search_text_internal(member)) ); >> CREATE INDEX >> jasen=# update member set id=id where id=441; >> ERROR: table row type and query-specified row type do not match >> DETAIL: Physical storage mismatch on dropped attribute at ordinal >> position 109. > > http://archives.postgresql.org/pgsql-bugs/2010-01/msg00089.php > > This is fixed for the next 8.4 update, but it's not going to be fixed in > 8.3.x. Suggest recreating the table without any dropped columns. If it breaks again next time someone drops a column that's not going to be suitable. It's a large table with several incices and relationships to and from other tables, if I re-create it (eg using SELECT ... INTO) I'd also need to redo all the triggers, indices, constraints, and relationships and then the next time I'd have track them all down again (in case they've been changed), there's too much risk of something breaking. I'll continue to use an index on column that's maintained by a trigger to store the tsvector (like the fulltextsearch docs suggest) until we're ready to upgrade our several clients to 8.4 or 9. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On 02/02/2010 06:40 PM, Tom Lane wrote:
> The difference with PQconnectdbParams is that the dbname might not be
> the first thing in the parameter array. I think that a straightforward
> implementation would have the effect of the expanded dbname overriding
> parameters given before it, but not those given after it. Consider
>
> keyword[0] = "port";
> values[0] = "5678";
> keyword[1] = "dbname";
> values[1] = "dbname = db user = foo port = ";
> keyword[2] = "user";
> values[2] = "uu";
>
> What I'm imagining is that this would end up equivalent to
> dbname = db user = uu port = . That's probably reasonable,
> and maybe even useful, as long as it's documented.
No doc changes yet, and I still have not corrected the earlier mentioned
issue,
> While I'm looking at this, there's another bogosity in the original
> patch: it neglected the PQsetdbLogin call in psql/command.c, meaning
> that doing \c would result in losing the application_name setting.
but I wanted to get feedback before going further.
This patch implements Tom's idea above. Note that I also rearranged the
parameters for the call from psql so that dbname is last, therefore
allowing a conninfo to override all other settings. The result looks like:
keywords[0] = "host";
values[0] = options.host;
keywords[1] = "port";
values[1] = options.port;
keywords[2] = "user";
values[2] = options.username;
keywords[3] = "password";
values[3] = password;
keywords[4] = "application_name";
values[4] = pset.progname;
keywords[5] = "dbname";
values[5] = (options.action == ACT_LIST_DB &&
options.dbname == NULL) ?
"postgres" : options.dbname;
keywords[6] = NULL;
values[6] = NULL;
Which produces:
# psql -U postgres "dbname=regression user=fred application_name='joe\'s
app'"
psql (8.5devel)
Type "help" for help.
regression=> select backend_start, application_name from pg_stat_activity ;
backend_start | application_name
---+--
2010-02-02 21:44:55.969202-08 | joe's app
(1 row)
regression=> select current_user;
current_user
--
fred
(1 row)
Are there any of the psql parameters that we do not want to allow to be
overridden by the conninfo string?
Joe
Index: src/bin/psql/startup.c
===
RCS file: /opt/src/cvs/pgsql/src/bin/psql/startup.c,v
retrieving revision 1.159
diff -c -r1.159 startup.c
*** src/bin/psql/startup.c 28 Jan 2010 06:28:26 - 1.159
--- src/bin/psql/startup.c 3 Feb 2010 05:41:54 -
***
*** 90,97
char *password = NULL;
char *password_prompt = NULL;
bool new_pass;
- const char *keywords[] = {"host","port","dbname","user",
- "password","application_name",NULL};
set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("psql"));
--- 90,95
***
*** 173,192
/* loop until we have a password if requested by backend */
do
{
! const char *values[] = {
! options.host,
! options.port,
! (options.action == ACT_LIST_DB &&
!options.dbname == NULL) ? "postgres" : options.dbname,
! options.username,
! password,
! pset.progname,
! NULL
! };
!
! new_pass = false;
!
! pset.db = PQconnectdbParams(keywords, values);
if (PQstatus(pset.db) == CONNECTION_BAD &&
PQconnectionNeedsPassword(pset.db) &&
--- 171,201
/* loop until we have a password if requested by backend */
do
{
! #define PARAMS_ARRAY_SIZE 7
! const char **keywords = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords));
! const char **values = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*values));
!
! keywords[0] = "host";
! values[0] = options.host;
! keywords[1] = "port";
! values[1] = options.port;
! keywords[2] = "user";
! values[2] = options.username;
! keywords[3] = "password";
! values[3] = password;
! keywords[4] = "application_name";
! values[4] = pset.progname;
! keywords[5] = "dbname";
! values[5] = (options.action == ACT_LIST_DB &&
! options.dbname == NULL) ?
! "postgres" : options.dbname;
! keywords[6] = NULL;
! values[6] = NULL;
!
! new_pass = false;
! pset.db = PQconnectdbParams(keywords, values, true /* expand conninfo string in dbname if found */);
! free(keywords);
! free(values);
if (PQstatus(pset.db) == CONNECTION_BAD &&
PQconnectionNeedsPassword(pset.db) &&
Index: src/interfaces/libpq/fe-connect.c
===
RCS file: /opt/src/cvs/pgsql/src/interfaces/libpq/fe-connect.c,v
r
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
Joe Conway writes: > Are there any of the psql parameters that we do not want to allow to be > overridden by the conninfo string? Actually, now that I think about it, psql shouldn't be setting application_name at all. It should be setting fallback_application_name, and I think that should be after the dbname so that it's not overridable. The way it's being done now destroys the usefulness of the PGAPPNAME environment variable. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On 02/02/2010 10:08 PM, Tom Lane wrote: > Joe Conway writes: >> Are there any of the psql parameters that we do not want to allow to be >> overridden by the conninfo string? > > Actually, now that I think about it, psql shouldn't be setting > application_name at all. It should be setting > fallback_application_name, and I think that should be after the dbname > so that it's not overridable. The way it's being done now destroys the > usefulness of the PGAPPNAME environment variable. Easily done. I'll fix psql/command.c and the documentation tomorrow and post another patch for review before committing. Should I also be looking to replace all (or most) other instances of PQsetdbLogin()? Thanks! Joe signature.asc Description: OpenPGP digital signature
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
Joe Conway writes: > Should I also be looking to replace all (or most) other instances of > PQsetdbLogin()? I think we at least wanted to fix pg_dump(all)/pg_restore. Not sure if the others are worth troubling over. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
