Re: [BUGS] BUG #4599: bugfix for contrib/dblink module
Oleksiy Shchukin wrote: The following bug has been logged online: Bug reference: 4599 Logged by: Oleksiy Shchukin Email address: oleksiy.shchu...@globallogic.com PostgreSQL version: 8.3.5 Operating system: all Description:bugfix for contrib/dblink module [snip] dblink.c fix In dblink.c:785 2nd argument for dblink_get_result(text,bool) is referenced as 'PG_GETARG_BOOL(2)', must be 'PG_GETARG_BOOL(1)'. This looks like a correct assessment. Will fix... Thanks! Joe -- 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 #4876: author of MD5 says it's seriously broken - hash collision resistance problems
Meredith L. Patterson wrote: Magnus Hagander wrote: this has implications for storing passwords as MD5 hashes. My That would be the only system use of MD5. What implications are those? We might want to consider using a safer hash for the password storage at some point, but from what I gather it's not really urgent for *that* use. It would be a lot more urgent if we weren't salting, but IIRC we are. If we really want something safer for system use in passwords, we ought to be using HMAC instead. I don't believe and weaknesses of MD5 have been found when it is used for HMAC. It has the added advantage that there is no direct storage of the password itself, even in hashed form. Joe -- 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 #4916: wish: more statistical functions (median, percentiles etc)
Joshua Tolley wrote: On Sat, Jul 11, 2009 at 02:12:22AM +0100, Richard Neill wrote: Thanks for your reply. Sadly, I haven't the time (or expertise) to write this myself. However, the feature would be really useful to have. I'd certainly be willing to make a £200 payment or donation in return. That's very nice of you to make the offer. Pending someone taking you up on it, you might consider your ability to write the functions in some procedural language. They would probably be easier to write, and you'd only have to make them handle data types you're planning to use them with. For instance, there's an example of PL/Perl versions available embedded in the code here: This stuff is pretty trivial to do with PL/R Joe -- 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] dblink generates orphaned connections
Tatsuhito Kasahara wrote:
> dblink generates orphaned connections when we failed on performing dblink()
> function.
> # But it occurs only when dblink('conn_str', 'sql', true). See following
> examples.
> I think that the dblink should not ereport() before PQfinish(conn) in
> dblink_record_internal() when we use temporary connection.
Thanks for the report. Patch applied to HEAD and 8.4 branch. Problem
introduced in 8.4
Joe
signature.asc
Description: OpenPGP digital signature
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. Will fix. Give me a day or so though. Joe signature.asc Description: OpenPGP digital signature
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 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
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
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
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
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
On 02/02/2010 10:23 PM, Tom Lane wrote:
> 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.
OK, this one includes pg_dump(all)/pg_restore and common.c from
bin/scripts (createdb, vacuumdb, etc). I still need to adjust the docs,
but other than that any remaining complaints?
Joe
Index: src/bin/pg_dump/pg_backup_db.c
===
RCS file: /opt/src/cvs/pgsql/src/bin/pg_dump/pg_backup_db.c,v
retrieving revision 1.85
diff -c -r1.85 pg_backup_db.c
*** src/bin/pg_dump/pg_backup_db.c 14 Dec 2009 00:39:11 - 1.85
--- src/bin/pg_dump/pg_backup_db.c 4 Feb 2010 03:57:42 -
***
*** 154,163
do
{
new_pass = false;
! newConn = PQsetdbLogin(PQhost(AH->connection), PQport(AH->connection),
! NULL, NULL, newdb,
! newuser, password);
if (!newConn)
die_horribly(AH, modulename, "failed to reconnect to database\n");
--- 154,187
do
{
+ #define PARAMS_ARRAY_SIZE 7
+ const char **keywords = malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords));
+ const char **values = malloc(PARAMS_ARRAY_SIZE * sizeof(*values));
+
+ if (!keywords || !values)
+ die_horribly(AH, modulename, "out of memory\n");
+
+ keywords[0] = "host";
+ values[0] = PQhost(AH->connection);
+ keywords[1] = "port";
+ values[1] = PQport(AH->connection);
+ keywords[2] = "user";
+ values[2] = newuser;
+ keywords[3] = "password";
+ values[3] = password;
+ keywords[4] = "dbname";
+ values[4] = newdb;
+ keywords[5] = "fallback_application_name";
+ values[5] = progname;
+ keywords[6] = NULL;
+ values[6] = NULL;
+
new_pass = false;
! newConn = PQconnectdbParams(keywords, values, true);
!
! free(keywords);
! free(values);
!
if (!newConn)
die_horribly(AH, modulename, "failed to reconnect to database\n");
***
*** 237,245
*/
do
{
new_pass = false;
! AH->connection = PQsetdbLogin(pghost, pgport, NULL, NULL,
! dbname, username, password);
if (!AH->connection)
die_horribly(AH, modulename, "failed to connect to database\n");
--- 261,293
*/
do
{
+ #define PARAMS_ARRAY_SIZE 7
+ const char **keywords = malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords));
+ const char **values = malloc(PARAMS_ARRAY_SIZE * sizeof(*values));
+
+ if (!keywords || !values)
+ die_horribly(AH, modulename, "out of memory\n");
+
+ keywords[0] = "host";
+ values[0] = pghost;
+ keywords[1] = "port";
+ values[1] = pgport;
+ keywords[2] = "user";
+ values[2] = username;
+ keywords[3] = "password";
+ values[3] = password;
+ keywords[4] = "dbname";
+ values[4] = dbname;
+ keywords[5] = "fallback_application_name";
+ values[5] = progname;
+ keywords[6] = NULL;
+ values[6] = NULL;
+
new_pass = false;
! AH->connection = PQconnectdbParams(keywords, values, true);
!
! free(keywords);
! free(values);
if (!AH->connection)
die_horribly(AH, modulename, "failed to connect to database\n");
***
*** 697,699
--- 745,748
else
return false;
}
+
Index: src/bin/pg_dump/pg_dumpall.c
===
RCS file: /opt/src/cvs/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.131
diff -c -r1.131 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 6 Jan 2010 03:34:41 - 1.131
--- src/bin/pg_dump/pg_dumpall.c 4 Feb 2010 03:55:45 -
***
*** 1618,1625
*/
do
{
new_pass = false;
! conn = PQsetdbLogin(pghost, pgport, NULL, NULL, dbname, pguser, password);
if (!conn)
{
--- 1618,1653
*/
do
{
+ #define PARAMS_ARRAY_SIZE 7
+ const char **keywords = malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords));
+ const char **values = malloc(PARAMS_ARRAY_SIZE * sizeof(*values));
+
+ if (!keywords || !values)
+ {
+ fprintf(stderr, _("%s: out of memory\n"), progname);
+ exit(1);
+ }
+
+ keywords[0] = "host";
+ values[0] = pghost;
+ keywords[1] = "port";
+ values[1] = pgport;
+ keywords[2] = "user";
+ values[2] = pguser;
+ keywords[3] = "password";
+ values[3] = password;
+ keywords[4] = "dbname";
+ values[4] = dbname;
+ keywords[5] = "fallback_application_name";
+ values[5] = progname;
+ keywords[6] = NULL;
+ values[6] = NULL;
+
new_pass = false;
! conn = PQconnectdbParams(keywords, values, true);
!
! free(keywords);
! free(values);
if (!conn)
{
Index: src/bin/ps
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On 02/04/2010 01:23 AM, Fujii Masao wrote: > On Thu, Feb 4, 2010 at 1:26 PM, Joe Conway wrote: >> OK, this one includes pg_dump(all)/pg_restore and common.c from >> bin/scripts (createdb, vacuumdb, etc). I still need to adjust the docs, >> but other than that any remaining complaints? > * expand_dbname is defined as a "bool" value in PQconnectdbParams() > and PQconnectStartParams(). But we should hide such a "bool" from > an user-visible API, and use an "int" instead? Yes, I suppose there is precedence for that. > * conninfo_array_parse() calls PQconninfoFree(str_options) as soon > as one "dbname" keyword is found. So if more than one "dbname" > keywords are unexpectedly specified in PQconnectdbParams(), the > str_options would be free()-ed doubly. Great catch -- thank you! Thanks for the review. I'll do a documentation update, make these changes, and commit later today if I don't hear any other objections. Joe signature.asc Description: OpenPGP digital signature
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On 02/04/2010 08:31 AM, Joe Conway wrote:
> On 02/04/2010 01:23 AM, Fujii Masao wrote:
>> On Thu, Feb 4, 2010 at 1:26 PM, Joe Conway wrote:
>>> OK, this one includes pg_dump(all)/pg_restore and common.c from
>>> bin/scripts (createdb, vacuumdb, etc). I still need to adjust the docs,
>>> but other than that any remaining complaints?
>
>> * expand_dbname is defined as a "bool" value in PQconnectdbParams()
>> and PQconnectStartParams(). But we should hide such a "bool" from
>> an user-visible API, and use an "int" instead?
>
> Yes, I suppose there is precedence for that.
>
>> * conninfo_array_parse() calls PQconninfoFree(str_options) as soon
>> as one "dbname" keyword is found. So if more than one "dbname"
>> keywords are unexpectedly specified in PQconnectdbParams(), the
>> str_options would be free()-ed doubly.
>
> Great catch -- thank you!
>
> Thanks for the review. I'll do a documentation update, make these
> changes, and commit later today if I don't hear any other objections.
Attached has both items fixed and documentation changes.
Joe
Index: doc/src/sgml/libpq.sgml
===
RCS file: /opt/src/cvs/pgsql/doc/src/sgml/libpq.sgml,v
retrieving revision 1.296
diff -c -r1.296 libpq.sgml
*** doc/src/sgml/libpq.sgml 28 Jan 2010 06:28:26 - 1.296
--- doc/src/sgml/libpq.sgml 4 Feb 2010 17:28:55 -
***
*** 98,104
Makes a new connection to the database server.
! PGconn *PQconnectdbParams(const char **keywords, const char **values);
--- 98,104
Makes a new connection to the database server.
! PGconn *PQconnectdbParams(const char **keywords, const char **values, int expand_dbname);
***
*** 115,120
--- 115,126
+When expand_dbname is non-zero, the
+dbname key word value is allowed to be recognized
+as a conninfo string. See below for details.
+
+
+
The passed arrays can be empty to use all default parameters, or can
contain one or more parameter settings. They should be matched in length.
Processing will stop with the last non-NULL element
***
*** 473,478
--- 479,502
is checked. If the environment variable is not set either,
then the indicated built-in defaults are used.
+
+
+ If expand_dbname is non-zero and
+ dbname contains an = sign, it
+ is taken as a conninfo string in exactly the same way as
+ if it had been passed to PQconnectdb(see below). Previously
+ processed key words will be overridden by key words in the
+ conninfo string.
+
+
+
+ In general key words are processed from the beginning of these arrays in index
+ order. The effect of this is that when key words are repeated, the last processed
+ value is retained. Therefore, through careful placement of the
+ dbname key word, it is possible to determine what may
+ be overridden by a conninfo string, and what may not.
+
+
***
*** 573,579
Make a connection to the database server in a nonblocking manner.
! PGconn *PQconnectStartParams(const char **keywords, const char **values);
--- 597,603
Make a connection to the database server in a nonblocking manner.
! PGconn *PQconnectStartParams(const char **keywords, const char **values, int expand_dbname);
***
*** 597,604
With PQconnectStartParams, the database connection is made
using the parameters taken from the keywords and
!values arrays, as described above for
!PQconnectdbParams.
--- 621,628
With PQconnectStartParams, the database connection is made
using the parameters taken from the keywords and
!values arrays, and controlled by expand_dbname,
!as described above for PQconnectdbParams.
Index: src/bin/pg_dump/pg_backup_db.c
===
RCS file: /opt/src/cvs/pgsql/src/bin/pg_dump/pg_backup_db.c,v
retrieving revision 1.85
diff -c -r1.85 pg_backup_db.c
*** src/bin/pg_dump/pg_backup_db.c 14 Dec 2009 00:39:11 - 1.85
--- src/bin/pg_dump/pg_backup_db.c 4 Feb 2010 03:57:42 -
***
*** 154,163
do
{
new_pass = false;
! newConn = PQsetdbLogin(PQhost(AH->connection), PQport(AH->connection),
! NULL, NULL, newdb,
! newuser, password);
if (!newConn)
die_horribly
Re: [BUGS] BUG #5304: psql using conninfo fails in connecting to the server
On 02/04/2010 09:37 AM, Joe Conway wrote: > On 02/04/2010 08:31 AM, Joe Conway wrote: >> On 02/04/2010 01:23 AM, Fujii Masao wrote: >>> On Thu, Feb 4, 2010 at 1:26 PM, Joe Conway wrote: >>>> OK, this one includes pg_dump(all)/pg_restore and common.c from >>>> bin/scripts (createdb, vacuumdb, etc). I still need to adjust the docs, >>>> but other than that any remaining complaints? >> >>> * expand_dbname is defined as a "bool" value in PQconnectdbParams() >>> and PQconnectStartParams(). But we should hide such a "bool" from >>> an user-visible API, and use an "int" instead? >> >> Yes, I suppose there is precedence for that. >> >>> * conninfo_array_parse() calls PQconninfoFree(str_options) as soon >>> as one "dbname" keyword is found. So if more than one "dbname" >>> keywords are unexpectedly specified in PQconnectdbParams(), the >>> str_options would be free()-ed doubly. >> >> Great catch -- thank you! >> >> Thanks for the review. I'll do a documentation update, make these >> changes, and commit later today if I don't hear any other objections. > > Attached has both items fixed and documentation changes. r4 patch committed Joe signature.asc Description: OpenPGP digital signature
Re: [BUGS] PostgreSQL-9.0alpha: jade required?
On 02/24/2010 08:43 AM, Lou Picciano wrote: > Tom - > > Didn't realize I was arm waving - was I? (Sometimes email falls well > short...) > > We've managed a build of PostgreSQL 9.0-alpha4 - nice! However, the # > make install command apparently(?) hiccups > on a dependency on Jade (we ain't usin' it!) I had forgotten to report it, but we saw this exact case at the Postgres booth at SCaLE on someone's laptop, and did the same workaround. We started with the alpha tarball. I believe the machine was Fedora, but not sure which. I tried to repeat the issue in a fresh CentOS VM when I got home but did not see the problem (perhaps because jade was part of the install -- will have to check). Related to this I have noticed in recent weeks on my own development machine that "make install" takes *much* longer, but only sporadically, due to the docs building. Joe signature.asc Description: OpenPGP digital signature
Re: [BUGS] Bug #467: Can't insert a value of 0 (zero) into a Bytea
> > Short Description
> > Can't insert a value of 0 (zero) into a Bytea type.
> >
> > Long Description
> > It does not zeem possible to insert a value of zero (0) into a bytea
type. A
> > lso, using '\134' (the octal code for a backslash) causes byteain() to
genera
> > te an error message.
> >
> > As a side issue, how can one tell a backslash followed by 3 digits (four
byte
> > s of data) from an encoded byte of data? It seems to me that byteaout()
shou
> > ld always output an octal escape sequence per byte, even if the
character is
> > printable. That way the result is unambiguous in meaning (even if it is
wast
> > eful of bytes).
>
> Further investigation provided the following information:
>
> 1. To insert a zero value the '\\000' sequence is required.
>
> 2. To insert a backslash, 4 backslashes are required (i.e. '')
>
> Therefore, to insert a backslash followed by the characters 1, 2, and 3
(four
> bytes of data), you would uses the sequence '123'. On retrieval from
the
> database, the sequence '\\123' would be returned.
>
> Can anyone confirm that this is correct. If it is, then this bug report
can be closed.
This was recently discussed on hackers (see
http://fts.postgresql.org/db/mw/msg.html?mid=1032591), but the short answer
is that you are correct (and that this is not a bug).
[root@jec-linux /root]# psql -U postgres test
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=# create table t1(f1 bytea);
CREATE
test=# insert into t1(f1) values('123');
INSERT 1482289 1
test=# select f1 from t1;
f1
---
\\123
(1 row)
test=# select octet_length(f1) from t1;
octet_length
--
4
(1 row)
test=# insert into t1(f1) values('\\000');
INSERT 1482290 1
test=# select f1 from t1 where f1 = '\\000';
f1
--
\000
(1 row)
test=# select octet_length(f1) from t1 where f1 = '\\000';
octet_length
--
1
(1 row)
HTH,
-- Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Bug #625: bytea data type problem/bug
[EMAIL PROTECTED] wrote: > Eric Lu ([EMAIL PROTECTED]) reports a bug with a severity of 4 > The lower the number the more severe it is. > > Short Description > bytea data type problem/bug > > Long Description > Hi, > > It happens when I was trying to test the avalaibility of the new binary data type > comes with ver 7.2. > As I read from the document, will take data from '\\000' (0d)to '\\377' >(255d). Although it saves data more than one byte, it looks odd to me that the first >digit of the binary data could never be greater than '3'. > Is it the way it should be? > Thanks! > > Eric Lu > The escaped values must be three-digits, octal (i.e. base 8) numbers, and in the range of 0 - 255 in decimal (base 10). Only the digits 0 - 7 are defined, and 377 octal == 255 decimal is the maximum allowable value. Anything starting with 4 - 7 (e.g. 400 octal == 256 decimal) is too big. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] BUG #2600: dblink compile with SSL missing libraries
Christopher Browne wrote: The following bug has been logged online: If I try to build dblink when PG is configured "--with-openssl", the build of the contrib module dblink breaks as follows: If I add, to the GCC command line, requests for libssl and libcrypto... -lssl -lcrypto e.g. - command line: [EMAIL PROTECTED]:/opt/rg/data_dba/build-farm/HEAD/pgsql.741430/ contrib/dblink $ /opt/prod/gcc-4.1.1/bin/gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -Wl,-bmaxdata:0x8000 -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE -o libdblink.so libdblink.a -Wl,-bE:libdblink.exp -L../../src/interfaces/libpq -L../../src/port -L/opt/freeware/lib -lpq -lpthread -lpthreads -lssl -lcrypto -Wl,-bI:../../src/backend/postgres.imp This builds fine without further complaint. Interesting. I build using "--with-openssl" all the time and have never had a problem. Can anyone comment on the appropriate Makefile changes for this? Thanks, Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2694: Memory allocation error when selecting array
Tom Lane wrote:
"Vitali Stupin" <[EMAIL PROTECTED]> writes:
The error "invalid memory alloc request size 4294967293" apears when
selecting array of empty arrays:
select ARRAY['{}'::text[],'{}'::text[]];
I can get a core dump off it too, sometimes. The problem is in
ExecEvalArray, which computes the dimension of the result as [1:2]
even though there are no elements to put in it.
Joe, what do you think about this? Offhand I think that the only
workable definition is that this case yields another zero-dimensional
array, but maybe there is another choice?
Sorry for the slow response -- I'm at the airport just heading home from
a marathon 30 day business trip.
I think producing another zero-dimensional result is the only way that
makes sense unless/until we change multidimensional arrays to really be
arrays of array-datatype elements. Right now they're two different things.
We should probably check all the other array operations to see if they
have comparable problems.
Yes -- I'll see if I can find the time over the next couple weeks while
home.
Joe
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2694: Memory allocation error when selecting array
Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
Sorry for the slow response -- I'm at the airport just heading home from
a marathon 30 day business trip.
Yow. Hope you get some time off...
Yeah, I just took a week. Next week I'm back to work and the week after
that I'm back to Germany for a few...
On looking at the code, I notice that this somewhat-related case works:
regression=# select array[null::text[], null::text[]];
array
---
{}
(1 row)
The reason is that null inputs are just ignored in ExecEvalArray. So
one pretty simple patch would be to ignore zero-dimensional inputs too.
This would have implications for mixed inputs though: instead of
regression=# select array['{}'::text[], '{a,b,c}'::text[]];
ERROR: multidimensional arrays must have array expressions with matching
dimensions
you'd get behavior like
regression=# select array[null::text[], '{a,b,c}'::text[]];
array
---
{{a,b,c}}
(1 row)
Which of these seems more sane?
I'm not sure I love either. I would think both NULL and empty array
expressions should be disallowed in this scenario, i.e.:
regression=# select array['{}'::text[], '{a,b,c}'::text[]];
ERROR: multidimensional arrays must have array expressions with
matching dimensions
regression=# select array[NULL::text[], '{a,b,c}'::text[]];
ERROR: multidimensional arrays must have array expressions with
matching dimensions
In both cases you are trying to construct a multidimensional array with
inconsistent dimensions.
On the other hand, building an N-dimension array from entirely empty
array expressions should just produce an empty array, while using all
NULL expressions should produce an N-dim array full of NULLs.
But as I've opined before, all of this seems to me to be much cleaner if
arrays were always one-dimensional, and array elements could also be
nested arrays (per SQL 2003). If we said that the cardinality of the
nested array is an integral part of the datatype, then I think you would
have:
regression=# select array['{}'::text[], '{a,b,c}'::text[]];
ERROR: nested arrays must have array expressions with matching
dimensions
regression=# select array[NULL::text[], '{a,b,c}'::text[]];
array
---
{NULL, {a,b,c}}
(1 row)
So maybe this is the behavior we should shoot for now?
Joe
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] BUG #3852: Could not create complex aggregate
Tom Lane wrote: "Sokolov Yura" <[EMAIL PROTECTED]> writes: create or replace function add_group(grp anyarray, ad anyelement, size int4) returns anyarray language plpgsql ... create aggregate build_group(anyelement, int4) ( SFUNC= add_group, STYPE = anyarray ); ERROR: argument declared "anyarray" is not an array but type anyarray After chewing on this for awhile, it seems to me that pg_aggregate.c is using enforce_generic_type_consistency() in a rather fundamentally different way than it's being used anywhere else. [snip] I think we could make enforce_generic_type_consistency() clearer by adding an additional argument "bool allow_poly" which specifies whether polymorphic "actual" argument and result types are allowed. [snip] lookup_agg_function() should always invoke enforce_generic_type_consistency(), with this argument "true". This sounds like a reasonable plan to me. Although this problem really goes quite far back, I think it's probably not interesting to back-patch further than 8.2, because AFAICS the interesting cases involve aggregates with more than one argument. I agree, especially since this is the first time anyone has complained. Did you want me to work on this? I could probably put some time into it this coming weekend. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3983: pgxs files still missing in win32 install (8.3.1)
Dave Page wrote: On Tue, Mar 18, 2008 at 12:47 AM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Mike Leahy wrote: > Is this actually a bug, or is there a specific reason that the pgxs > files omitted in the windows installer? I noticed the 8.3.1 installer > has been posted (as the updates had appeared on my linux machines). From > what I can tell, the 8.3.1 version of the win32 installer is still > missing pgxs.mk and any related files. It is a bug in the Win32 installer. Please report it on the Win32 installer project, http://pgfoundry.org/projects/pginstaller/ It's not an installer bug - pgxs doesn't make sense to the VC++ PostgreSLQ build because we don't use (or configure) any of the build system on which it relies. I wonder if we can either build whatever is needed to make pgxs work (I'm guessing Makefile.global and more) when we run the perl scripts that generate the project files, or come up with a VC++ alternative to pgxs. I'm finally getting back to this, and finding that plr.dll built against a MinGW compiled postgres will not load in running cluster built with VC++ (i.e. the standard binary install). Is that to be expected? If so, can you give me pointers (either direct guidance or literal URLs) on how to build postgres and related extensions with VC++? Thanks, Joe -- 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 #3983: pgxs files still missing in win32 install (8.3.1)
Magnus Hagander wrote: In general, mingw built modules should load just fine in msvc built postgres. AFAIK, that's how PostGIS does it for 8.3 (though I know Mark is working on getting MSVC build support for them). Debugging may be a bit harder (since they use different kinds of debug symbols - postgres uses Windows style and mingw uses mingw style), but it should certainly load. What trouble exactly are you seeing? Basically, "Procedure not found", even though it is there. Also note that the same R.dll is being used from the MinGW Postgres installation (where plr loads successfully) and the MSVC Postgres. Joe p.s. actual output below 8<-- Welcome to psql 8.3.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. postgres=# load '$libdir/dblink'; LOAD postgres=# load '$libdir/plr'; ERROR: could not load library "C:/Program Files/PostgreSQL/8.3/lib/plr.dll": The specified procedure could not be found. postgres=# CREATE TYPE plr_environ_type AS (name text, value text); CREATE TYPE postgres=# CREATE OR REPLACE FUNCTION plr_environ () postgres-# RETURNS SETOF plr_environ_type postgres-# AS '$libdir/plr','plr_environ' postgres-# LANGUAGE 'C'; ERROR: could not load library "C:/Program Files/PostgreSQL/8.3/lib/plr.dll": The specified procedure could not be found. Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\jconway>dir "C:\Program Files\PostgreSQL\8.3\lib" Volume in drive C has no label. Volume Serial Number is A006-8372 Directory of C:\Program Files\PostgreSQL\8.3\lib [...] 03/17/2008 03:49 AM57,344 dblink.dll [...] 03/17/2008 03:47 AM28,264 libpq.lib [...] 04/06/2008 12:50 PM 686,579 plr.dll -- 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 #3983: pgxs files still missing in win32 install (8.3.1)
Magnus Hagander wrote: Could this be somethingl ike missing PGDLLIMPORT specifications in your addon module or something like that? Try checking the names of the functions that are actually exported using "depends" or a similar tool. Ah, that sounds likely, since I have never had to worry about explicit exports with PL/R before. Can you point me to an example or cheat sheet on what I need to do? ERROR: could not load library "C:/Program Files/PostgreSQL/8.3/lib/plr.dll": The specified procedure could not be found. Actually, this looks like perhaps the backend is unable to load a DLL that plr.dll depends on. Again, the "depends" tool can hopefully show you what's missing there. That's what I was originally thinking (R.dll), but now I suspect the exported functions is probably the issue. I'll check this out when I get home tonight. Thanks, Joe -- 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 #4203: perform dblink() in begin/exception returns wrong SQLSTATE code
Tom Lane wrote: "Henry Combrinck" <[EMAIL PROTECTED]> writes: Description:perform dblink() in begin/exception returns wrong SQLSTATE code The code returned is always 42601 (syntax_error) irrespective of the actual error (eg, unique_violation). Yeah, the dblink code should probably try a bit harder to propagate the original error fields. I'm inclined to think that it should propagate sqlstate/message/detail/hint verbatim, and indicate the fact that this happened on a dblink connection as CONTEXT, rather than structuring the ereport the way it does now. Joe, what do you think? Sounds reasonable. Do you think this is a bug fix or an 8.4 enhancement? I will try to take a closer look at the specific fix this weekend. Joe -- 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] pg_dumpall forces plain text format
grant wrote: > You could fake some of this (select only) by using the dblink stuff in > contrib. You could link back to yourself and make it work. Maybe if > you REALLY need it, you could modify dblink to allow updates as well as > selects. If you really need it that bad, you have the source, write it. dblink in cvs will work on 7.2.x and supports UPDATE/INSERT/DELETE. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #712: Documentation Section 3.4 Binary Strings
[EMAIL PROTECTED] wrote: > David Clark ([EMAIL PROTECTED]) reports a bug with a severity > Table 3-7 SQL Literal escaped octets shows the input escape > representation for a single quote as '\\'' , but the third paragraph > below table 3-8 SQL Output Escaped Octets says that the single quote > must be input as '\'' Nice catch. '\'' is correct as shown in the example in Table 3-7. > > Also in the same paragraph mentioned above it says input for the > single quote must be '\'' (or '\\134') shouldn't this be (or '\\047') Also a bug. Should be '\\047', as you pointed out. Thanks for the report! I'll submit a patch. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #728: Interactions between bytea and character encoding
Anders Hammarquist wrote: >>[EMAIL PROTECTED] writes: >> >>>If a byte string that is not valid unicode is inserted into a bytea >>>column, analyze will fail unless the data was tagged as bytea in the >>>insert. >> >>Your example produces no failure for me. You'd better be more specific >>about which PG version you're running, on what platform, with what >>configure options and what database encoding, etc. > Ah, sorry about that. It's 7.2.1, in the Debian package incarnation > 7.2.1-2. The database and the client encoding are both unicode. These > are the setting from postmaster.conf (nothing strange): I can confirm this is a problem on 7.2.1, but cvs tip works fine. It is not related to the form of the insert but rather the fact that with a one tuple table, pg_verifymbstr() never gets called (where the error is raised). In fact, textin never gets called either. But once there are two tuples, they do. Here's the backtrace from 7.2.1: Breakpoint 1, pg_verifymbstr (mbstr=0x837a698 "42", len=2) at wchar.c:541 541 if (pg_database_encoding_max_length() <= 1) (gdb) bt #0 pg_verifymbstr (mbstr=0x837a698 "42", len=2) at wchar.c:541 #1 0x08149c26 in textin (fcinfo=0xbfffeca0) at varlena.c:191 #2 0x08160579 in DirectFunctionCall1 (func=0x8149c00 , arg1=137864856) at fmgr.c:657 #3 0x080bbffa in update_attstats (relid=74723, natts=2, vacattrstats=0x8379f58) at analyze.c:1740 #4 0x080ba180 in analyze_rel (relid=74723, vacstmt=0x8378110) at analyze.c:350 . . . Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #728: Interactions between bytea and character encoding
Tom Lane wrote: > Ah. So the issue is that ANALYZE tries to do textin(byteaout(...)) > in order to produce a textual representation of the most common value > in the BYTEA column, and apparently textin feels that the string > generated by byteaout is not legal text. While Joe says that the > problem has gone away in CVS tip, I'm not sure I believe that. I didn't either, except I tried it and it worked ;-) But you're undoubtedly correct that there are other cases which would break the current code. > A possible answer is to change the pg_statistics columns from text to > some other less picky datatype. (bytea maybe ;-)) Or should we > conclude that text is broken and needs to be fixed? Choice #3 would > be "bytea is broken and needs to be fixed", but I don't care for that > answer --- if bytea can produce an output string that will break > pg_statistics, then so can some other future datatype. BYTEA sounds like the best answer to me. TEXT is supposed to honor character set specific peculiarities, while bytea should be able to represent any arbitrary set of bytes. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #728: Interactions between bytea and character encoding
Bruce Momjian wrote: > Does this mean we don't have to esacpe >0x7f when inputting bytea > anymore? I seem to remember that bytea data was run through the multibute code for some reason, and I don't recall seeing that changed. ISTM that we shouldn't force bytea thought multibyte functions at all. The UNKNOWNIN patch did address part of the problem, just not all of it. Previously all 'unknown' data was initially cast as TEXT, and thus was subject to multibyte character set interpretation. But there was another execution path that was not dealt with. I'll search the archives for the thread. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #728: Interactions between bytea and character encoding
Joe Conway wrote: > Bruce Momjian wrote: > >> Does this mean we don't have to esacpe >0x7f when inputting bytea >> anymore? > > > I seem to remember that bytea data was run through the multibute code > for some reason, and I don't recall seeing that changed. ISTM that we > shouldn't force bytea thought multibyte functions at all. > > The UNKNOWNIN patch did address part of the problem, just not all of it. > Previously all 'unknown' data was initially cast as TEXT, and thus was > subject to multibyte character set interpretation. But there was another > execution path that was not dealt with. I'll search the archives for the > thread. > Here's the remaining issue that I remembered; see: http://archives.postgresql.org/pgsql-hackers/2002-04/msg00256.php The gist of this is that when client and server encoding don't match, pg_do_encoding_conversion() gets called, regardless of data type. This is the *wrong thing* to do for BYTEA data, I think. Fixing this, combined with the UNKNOWNIN/OUT fix we did earlier, should eliminate the need to escape the high bit characters when inputting bytea. The only characters which *should* need to be escaped are the ones originally escaped by PQescapeBytea. IMHO of course ;-) Joe Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug #736: Old bug is back!! Drop database bug
[EMAIL PROTECTED] wrote: > Chester Chee ([EMAIL PROTECTED]) reports a bug with a severity > of 2 The lower the number the more severe it is. > > Short Description Old bug is back!! Drop database bug > > Long Description PostgreSQL version 7.2.1 running on RedHat 7.3 with > all the latest patches installed. > > Step to reproduce the problem: 0) create database junk; 1) create > table more_junk ; 2) drop database junk; 3) create database junk; > 4) I am expecting to see database junk do not have any tables, but > if list the tables... all the previous tables are back with data in > it!! Try connecting to the template1 database, and then do \dt. Do you see the tables in question? Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Bug #728: Interactions between bytea and character encoding
Bruce Momjian wrote: > I am hoping for a patch for this for 7.3. Added to open items: > > Fix bytea to not encode input string > I said: > Here's the remaining issue that I remembered; see: > http://archives.postgresql.org/pgsql-hackers/2002-04/msg00256.php > > The gist of this is that when client and server encoding don't match, > pg_do_encoding_conversion() gets called, regardless of data type. > This is the *wrong thing* to do for BYTEA data, I think. Fixing this, > combined with the UNKNOWNIN/OUT fix we did earlier, should eliminate > the need to escape the high bit characters when inputting bytea. The > only characters which *should* need to be escaped are the ones > originally escaped by PQescapeBytea. IMHO of course ;-) Tatsuo or Tom can answer this better than me, but I don't think this can be fixed without a fe/be protocol change, so I'd guess it's a 7.4 issue. But, if there is a way to do it now, and someone gives me a clue how to proceed, I'll try to get a patch together. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Schemas not available for pl/pgsql %TYPE....
Sean Chittenden wrote: > Not a problem there. I walked around the code for a bit, made a few > hacks to see how things are working, and I can tell you strait up that > if you'd like this by 7.3, it won't be happening from me. <:~) I'm > stretched kinda thin as is and don't think I'll be able to get this > working correctly with time to test by release. I can send you the > patch I've got for the lexer, but that was chump. If you want to send me what you've done so far, I'll take a look and see if I can figure it out. I think this is probably a must do item for 7.3. Any further guidance or thoughts? Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] some other backend died abnormally
fredrik chabot wrote: > connection to server was lost > > I get more or less frequently. > > what to do? Start by giving us more information. What version of Postgres? What platform (Linux, etc)? What does the relevant portion of your database schema look like? What repeatable series of actions causes the problem? Is there a core file and have you looked at it in a debugger? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Creating server-side functions: one simple error
Ruslan A Dautkhanov wrote: > Btw, how I can convert TEXT field to a CString one? I had tried to use > char *cnum = PG_GETARG_CSTRING(0) instead of > text tnum = PG_GETARG_NAME(0), but it's not worked properly. Is any function > to convert text object to a null-terminated string??? Thanks again. > I like to use the following macros: /* convert C string to text pointer */ #define GET_TEXT(cstrp) \ DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp))) /* convert text pointer to C string */ #define GET_STR(textp) \ DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp))) then you can do, e.g. char *cnum = GET_STR(PG_GETARG_TEXT_P(0)); BTW, there are lots of good examples of C functions in contrib. HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] user name lost from dictionary
Elielson Fontanezi wrote: > GRANT ALL on "recursos_materiais" to "user_fomacao_des"; --< Here is the > point in question [...snip...] > REVOKE ALL on "recursos_materiais" from PUBLIC; > GRANT ALL on "recursos_materiais" to "131"; --< What is it? > > Is that a bug? It looks like someone dropped user_fomacao_des. What happens if you run: select * from pg_user; ? You can fix this by doing: CREATE USER user_fomacao_des WITH SYSID 131 PASSWORD 'password'; HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Creating server-side functions: one simple error
elein (by way of elein ) wrote: > This will not work if there is no EOS on the data portion of the > string. Text fields are not usually stored with the EOS on them, > are they? Yes, the TEXT data type is NULL terminated. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
bogus varno EXPLAIN bug (was Re: [BUGS] Explain analyze gives bogusvarno for dblink views)
Kris Jurka wrote: This behavior is present in 7.3 as well. On Thu, 5 Dec 2002, Kris Jurka wrote: Using the old < 7.3 version of dblink on 7.4devel gives a "get_names_for_var: bogus varno 5" error. I can confirm this both on cvs tip (pulled after noon PST today) and 7.3 stable branch. It is not related to dblink, but rather the backend. Here's a (contrived) script based on Kris's example to trigger it: CREATE TABLE table1 (a int); CREATE TABLE table2 (a int, b int); INSERT INTO table1 (a) VALUES (1); INSERT INTO table2 (a,b) VALUES (1,1); INSERT INTO table2 (a,b) VALUES (1,2); CREATE OR REPLACE FUNCTION func1(int) RETURNS setof int AS ' select a from table2 where a = $1 ' LANGUAGE 'sql' WITH (isstrict); CREATE OR REPLACE FUNCTION func2(int,int) RETURNS int AS ' select $1 * $2 ' LANGUAGE 'sql' WITH (isstrict); CREATE VIEW v1 AS SELECT func2(t1.f1,3) as a FROM (SELECT func1(1) as f1) AS t1; DROP VIEW v2; CREATE VIEW v2 AS SELECT func2(t2.f1,3) as a, func2(t2.f1,5) as b FROM (SELECT func1(1) as f1) AS t2; SELECT * FROM v1,v2 WHERE v1.a=v2.a AND v1.a=3; EXPLAIN ANALYZE SELECT * FROM v1,v2 WHERE v1.a=v2.a AND v1.a=3; Here's a backtrace: #0 elog (lev=20, fmt=0x8211800 "get_names_for_var: bogus varno %d") at elog.c:114 #1 0x0815e53c in get_names_for_var (var=0x82d07ec, context=0xbfffe9c0, schemaname=0xbfffe8b0, refname=0xbfffe8b4, attname=0xbfffe8b8) at ruleutils.c:1806 #2 0x0815e6ed in get_rule_expr (node=0x82d07ec, context=0xbfffe9c0, showimplicit=1 '\001') at ruleutils.c:1938 #3 0x0815eed3 in get_oper_expr (expr=0x0, context=0xbfffe9c0) at ruleutils.c:2282 #4 0x0815e7de in get_rule_expr (node=0x82d0b54, context=0xbfffe9c0, showimplicit=1 '\001') at ruleutils.c:1972 #5 0x0815eed3 in get_oper_expr (expr=0x0, context=0xbfffe9c0) at ruleutils.c:2282 #6 0x0815e7de in get_rule_expr (node=0x82d0b9c, context=0xbfffe9c0, showimplicit=0 '\0') at ruleutils.c:1972 #7 0x0815cfef in deparse_expression (expr=0x82d0b9c, dpcontext=0x0, forceprefix=0 '\0', showimplicit=0 '\0') at ruleutils.c:872 #8 0x080ca75a in show_upper_qual (qual=0x82d1d50, qlabel=0x81df318 "Filter", outer_name=0x5 , outer_varno=1, outer_plan=0x0, inner_name=0x819479b "", inner_varno=0, inner_plan=0x0, str=0x82d7668, indent=3, es=0x82e4b58) at explain.c:812 #9 0x080ca01e in explain_outNode (str=0x82d7668, plan=0x82d1d6c, planstate=0x82d4674, outer_plan=0x0, indent=3, es=0x82d7a58) at explain.c:570 #10 0x080c9d3a in explain_outNode (str=0x82d7668, plan=0x82d2098, planstate=0x82d2560, outer_plan=0x0, indent=0, es=0x82d7a58) at explain.c:614 #11 0x080c992b in ExplainOneQuery (query=0x82d7668, stmt=0x82bb9e8, tstate=0x82c06c8) at explain.c:198 #12 0x080c9745 in ExplainQuery (stmt=0x82bb9e8, dest=Remote) at explain.c:102 #13 0x081388a3 in pg_exec_query_string (query_string=0x82bb9e8, dest=Remote, parse_context=0x8287574) at postgres.c:789 #14 0x0813976c in PostgresMain (argc=5, argv=0xbfffee70, username=0x8279f19 "postgres") at postgres.c:2016 #15 0x0811e30e in DoBackend (port=0x8279de8) at postmaster.c:2293 #16 0x0811de7a in BackendStartup (port=0x8279de8) at postmaster.c:1915 #17 0x0811cf9d in ServerLoop () at postmaster.c:1002 #18 0x0811c915 in PostmasterMain (argc=3, argv=0x825cc78) at postmaster.c:781 #19 0x080f930f in main (argc=3, argv=0xb7e4) at main.c:209 Note the line: #8 0x080ca75a in show_upper_qual (qual=0x82d1d50, qlabel=0x81df318 "Filter", outer_name=0x5 , I'm still trying to understand the root cause, but any pointers would be appreciated. Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: bogus varno EXPLAIN bug (was Re: [BUGS] Explain analyze gives
Tom Lane wrote: Ah, thanks for the simplified test case. This is undoubtedly my fault ... will look into it. It is probably somewhat related to the join alias problem found yesterday (ie, somebody somewhere is trying to use the wrong rangetable list to interpret a Var node). I spent a bit more time on it last night. Here's an even simpler example: CREATE TABLE table1 (a int); CREATE TABLE table2 (a int, b int); INSERT INTO table1 (a) VALUES (1); INSERT INTO table2 (a,b) VALUES (1,1); INSERT INTO table2 (a,b) VALUES (1,2); CREATE OR REPLACE FUNCTION func1(int) RETURNS setof int AS ' select a from table2 where a = $1 ' LANGUAGE 'sql' WITH (isstrict); CREATE OR REPLACE FUNCTION func2(int,int) RETURNS int AS ' select $1 * $2 ' LANGUAGE 'sql' WITH (isstrict); regression=# SELECT t2.a1 FROM (SELECT func2(t1.f1,3) as a1 FROM (SELECT func1(1) as f1) AS t1) AS t2 WHERE t2.a1 = 3; a1 3 3 (2 rows) regression=# EXPLAIN SELECT t2.a1 FROM (SELECT func2(t1.f1,3) as a1 FROM (SELECT func1(1) as f1) AS t1) AS t2 WHERE t2.a1 = 3; ERROR: get_names_for_var: bogus varno 2 regression=# EXPLAIN SELECT t2.a1 FROM (SELECT func2(t1.f1,3) as a1 FROM (SELECT func1(1) as f1) AS t1) AS t2;QUERY PLAN Subquery Scan t1 (cost=0.00..0.01 rows=1 width=0) -> Result (cost=0.00..0.01 rows=1 width=0) (2 rows) The problem is triggered by the WHERE clause. I was struggling as to where to be looking. BTW, it was still there after I sync'd up with cvs last night. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] ON DELETE triggers don't work as documented
Mike Glover wrote: Please enter a FULL description of your problem: The docs state (section 23.9): If a non-NULL value is returned then the operation proceeds with that row value. Note that returning a row value different from the original value of NEW alters the row that will be inserted or updated. ^ the above suggests that returning NEW for a delete should cause the delete to proceed. In fact, I've found it necessary to return a record with the row format of the table and all empty fields. No, actually it suggests that returning NEW should cause an *update* or *insert* to proceed. In the case of a delete, NEW is not set. See a few lines above: NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in ROW level triggers. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in ROW level triggers. It is perhaps confusing, but probably necessary so that a single function can handle inserts, updates, and deletes (see TG_OP). Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] "ERROR: Query-specified return tuple and actual function
Andrzej Kosmala wrote: PostgreSQL 7.3 on Linux After dropping column functions return error message: "ERROR: Query-specified return tuple and actual function return tuple do not match" Hmmm, looks like nodeFunctionscan.c:tupledesc_mismatch needs to be taught about attisdropped. I'll submit a patch this evening if no one else gets to it first. Thanks for the report. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] "ERROR: Query-specified return tuple and actual function
Tom Lane wrote: > Joe Conway <[EMAIL PROTECTED]> writes: >> Hmmm, looks like nodeFunctionscan.c:tupledesc_mismatch needs to be >> taught about attisdropped. I'll submit a patch this evening if no >> one else gets to it first. > > Actually, I believe I deliberately left it like that because I was > concerned about what would happen in this scenario. I don't think > that changing tupledesc_mismatch to ignore attisdropped columns will > make things work nicely. If a function is expecting to return > (a,b,c) and then you drop b from the table that defines its return > type, is the function likely to magically return (a,c)? I doubt it. Assuming tupledesc_mismatch ignored attisdropped attributes, and the function still output the dropped attribute, the error would be triggered due to a mismatch. But then you could adjust your function and be on your merry way. In the case where the function is returning a whole table tuple (e.g. select * from...), everthing should work correctly (and as expected). Without this change, any table that has had a column dropped would not be able to be used as a function's return type at all. > Letting the code get past the mismatch check is likely to result in > core dumps. I don't see how. The code would still ensure that the returned tuple and defined tuple match, wouldn't it? start with table test, attributes (a,b,c) drop attribute b from test tupledesc_mismatch checks that function's output is (a,c) if function actually outputs (a,c), no problem Am I just being thick-headed? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #915: problem with returning setof with double precision
[EMAIL PROTECTED] wrote: Daniel Brozek ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description problem with returning setof with double precision values Long Description I have got the table and the function (look at example code). After executing this function I have got NULL values in the place of double precision columns. But in database those values are set. Simple SELECT (select * from service) from this table works propertly - double precision columns have their proper values. I am working with 7.3.2 version of Postgresql. Can you send a complete example? We need minimal table definition and sample data that reliably reproduces the problem. Also what OS and compiler? I'm not able to reproduce the problem here on 7.3.2 or cvs tip (see below): CREATE TABLE service( service_id int, val float8 ); insert into service values(1,1.23); insert into service values(2,2.34); CREATE OR REPLACE FUNCTION get_krd_info(INTEGER) RETURNS SETOF service AS ' DECLARE l_service_id ALIAS FOR $1; l_service service%ROWTYPE; BEGIN SELECT INTO l_service service.* FROM service WHERE service.service_id = l_service_id; RETURN NEXT l_service; RETURN; END; ' LANGUAGE 'plpgsql'; regression=# select * from get_krd_info(1); service_id | val +-- 1 | 1.23 (1 row) regression=# select * from get_krd_info(2); service_id | val +-- 2 | 2.34 (1 row) BTW, there's no reason to declare that function to return SETOF unless you loop through the results. As declared, it will never return more than one row. test=# insert into service values(1,3.45); INSERT 14266713 1 test=# select * from service; service_id | val +-- 1 | 1.23 2 | 2.34 1 | 3.45 (3 rows) test=# select * from get_krd_info(1); service_id | val +-- 1 | 1.23 (1 row) You may as well define it as: CREATE OR REPLACE FUNCTION get_krd_info(INTEGER) RETURNS service AS ' DECLARE l_service_id ALIAS FOR $1; l_service service%ROWTYPE; BEGIN SELECT INTO l_service service.* FROM service WHERE service.service_id = l_service_id; RETURN l_service; END; ' LANGUAGE 'plpgsql'; Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] bug in the concatenation of the selection...
Andres Cuevas wrote: If data1 or data2 are NULL the request is NULL Which is the correct behavior, not a bug. See: http://techdocs.postgresql.org/guides/BriefGuideToNulls HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] error in docs
andrea gelmini wrote: test=# SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 1; ERROR: Unable to identify an operator '*=' for types 'integer[]' and 'integer' You will have to retype this query using an explicit cast There's nothing wrong with the docs (well, at least not with respect to your specific problem), you just need to read them again. Here's a quote from the link you supplied: "However, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is unknown. Although it is not part of the primary PostgreSQL distribution, there is an extension available that defines new functions and operators for iterating over array values. Using this, the above query could be:" <...snip...> "To install this optional module, look in the contrib/array directory of the PostgreSQL source distribution." I have no idea how to install contrib/array using debian's package manager, but that's what you need to do. HTH, Joe ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Database Grants Bug
Marcus England wrote:
Grants do not work at the database level using the syntax mentioned in
the documentation. i.e.:
GRANT ALL ON DATABASE dbname TO GROUP groupname;
Or
GRANT ALL ON DATABASE dbname TO username;
Works here:
regression=# select version();
version
-
PostgreSQL 7.3.3 on i686-redhat-linux-gnu, compiled by GCC 2.96
(1 row)
regression=# GRANT ALL ON DATABASE regression TO GROUP grp1;
GRANT
regression=# GRANT ALL ON DATABASE regression TO user1;
GRANT
You need to be more specific in what you mean by "do not work". Do you
get an error? What exactly is not working?
Perhaps you expect more than you should -- re-read the docs,
specifically the section quoted here:
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...]
In the context of DATABASE, ALL means "CREATE & TEMPORARY & TEMP",
nothing more, nothing less. Further reading provides:
CREATE
For databases, allows new schemas to be created within the database.
TEMPORARY
TEMP
Allows temporary tables to be created while using the database.
Are these not working?
HTH,
Joe
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [BUGS] Database Grants Bug
Marcus England wrote:
IMHO, this is confusing and limiting for Administrators who wish to
grant privileges beyond CREATE, TEMPORARY, and TEMP across all tables in
a database. Something I believe most, if not all other DBMS's do. "ALL"
isn't very consistent.
Again, I don't know what your definition of "most, if not all other
DBMS's" is, but a quick read through my MSSQL2000 manual indicates SQL
Server is no different from Postgres in this regard. Same for Oracle 9i.
I'd say that covers the majority of DBMS installations. I don't have a
DB2 manual handy to check.
Reading the comments in the documentation, apparently I'm not the only
one who's confused about ALL.
True, it seems to come up reasonably frequently. But the docs are pretty
clear if you read them carefully.
And if you search the mailing list archives, you'll find more than one
script or function posted that allows GRANTs on all the tables in a
database, for instance (including one by me). The function is pretty
simple; here it is again for your convenience (not extensively tested --
use at your own risk, modify to suit, etc, etc):
CREATE OR REPLACE FUNCTION grant_all(text) RETURNS TEXT AS '
DECLARE
rel record;
sql text;
BEGIN
FOR rel IN SELECT pg_catalog.quote_ident(c.relname) AS relname FROM
pg_catalog.pg_class c WHERE c.relkind = ''r'' AND c.relnamespace NOT IN
(select oid from pg_catalog.pg_namespace where nspname like ''pg\_%'')
AND pg_catalog.pg_table_is_visible(c.oid) LOOP
sql := ''grant all on '' || rel.relname || '' to '' || $1;
RAISE NOTICE ''%'', sql;
EXECUTE sql;
END LOOP;
RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';
create user foo;
select grant_all('foo');
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [BUGS] PG 7.4 BETA 3: Bug in NULL arrays updating
Tom Lane wrote:
Bertrand Petit <[EMAIL PROTECTED]> writes:
When updating a NULL cell which is an array of something,
setting an adressed member of a non existent array, the value of the
cell is not changed.
Assigning to a member of a NULL array has always yielded another NULL
array. While I've never been particularly satisfied with that behavior
either, it has some logical symmetry to it. What do you think the
behavior ought to be? (In particular, if a non-null array should
result, where do we get its dimensions and subscripts from?)
I think the behavior is correct. An analogy I is text concatenation. If
I concatenate 'a' to NULL::text, I get NULL. But if I concatenate 'a' to
an empty text value, '', I get 'a'.
Similarly if you assign to an element of an empty array, '{}', you get
an array with the one appended element. Not sure if this works pre-7.4
though -- I know I made some changes related to this, but I forget the
exact details.
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Bug Report
Branden R. Williams wrote: POSTGRESQL BUG REPORT TEMPLATE Your name : Branden R. Williams Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : Intel Pentium Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.18 ELF PostgreSQL version (example: PostgreSQL-7.3.4): PostgreSQL-7.3.4 Compiler used (example: gcc 2.95.2) : Your RPM, RH 8. Please enter a FULL description of your problem: When using the replace() function, it appears that some of the output is filtered through a printf variant. If you have legitimate db characters that are also printf flags, it causes big problems. This was fixed for 7.3.4 (or so I thought); see: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/utils/adt/varlena.c.diff?r1=1.92&r2=1.92.2.1 Are you sure you don't have something earlier? Was does select version(); show? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] Autocomplete on Postgres7.4beta5 not working?
Alvaro Herrera Munoz wrote: On Tue, Oct 28, 2003 at 04:48:59PM -0500, Tom Lane wrote: AFAICT there was no discussion about this issue when the patch was proposed and applied. But now that the point is raised I have to say that I don't like this change. I don't think system catalogs should be excluded from tab completion. They never were before 7.4, and I have not seen anyone complaining about that, other than Ian. I found it very irritating at first, but when I discovered that I could tab my way to syscatalogs by using "pg_catalog." as prefix, I started feeling it was actually a nice behavior. I found it similarly irritating at first, and it continues to irritate me. But that may be because I use system tables more frequently than the average Joe ;-) I guess I'd be happy to see tab completion reinstated for system tables (except toast tables as Tom suggested nearby), but I don't feel particularly zealous about it. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] postgresql-7.4RC1 - unrecognized privilege type
Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: I think we should fix it but not force an initdb --- information_schema is new and I am not sure how many people are using it. Yeah, I'm leaning that way too. We could publicize a script to fix the problem in any beta5 or RC1 databases that people don't want to re-initdb. I agree, and this brings up a question that I've pondered before. Why do we ever *require* and initdb when only metadata has changed (i.e. the contents of the system catalogs, not catalog or page structure)? ISTM that we ought to distinguish between catalog version, meaning something structural has changed, and catalog-data version which is correctible by running a script. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] failed to lookup type 0
Vernon Smith wrote: When I try to delete data from one table, I get this error: ERROR: get_typdefault: failed to lookup type 0 I can't find any reasons other than a bug. The PG version is 7.3.4 And neither can we without some details. Please post the results of \d tablename , the definitions of any trigger functions (particularly any "on delete" triggers), and if possible a complete reproducible example. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] bytea, index and like operator
Alvar Freude wrote:
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
begin;
create table test (b bytea);
create index tst_idx on test(b);
insert into test values ('\001abc\006');
insert into test values ('\001xabc\006');
insert into test values ('\001\002abc\006');
insert into test values ('\000\001\002abc\006');
insert into test values ('\002\003abc\006');
Note that bytea input strings should be escaped with doubled
backslashes, because the string literal parser consumes 1 layer, and the
byteain function consumes another. See:
http://www.postgresql.org/docs/7.3/static/datatype-binary.html
In the strings above, the string literal parser will turn, e.g., "\001"
into the single octet '\1' anyway, and byteain will accept it just fine.
However "\000" will become '\0', and since byteain requires a null byte
terminator, you are actually inserting an empty string into test.b for
that row:
regression=# select b, b = '' from test;
b| ?column?
-+--
\001abc\006 | f
\001xabc\006| f
\001\002abc\006 | f
| t
\002\003abc\006 | f
(5 rows)
select * from test where b like '\001%';
This is weird. I'm sure it worked at one time -- will research.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1000: Testing new bugs form.
PostgreSQL Bugs List wrote: The following bug has been logged online: Bug reference: 1000 Logged by: Dave Page Email address: [EMAIL PROTECTED] PostgreSQL version: 7.5 Dev Operating system: FreeBSD www.postgresql.com 4.8-STABLE FreeBSD 4.8-STABLE #5: Sat Sep 20 14:56:14 ADT 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/kernel i386 Description:Testing new bugs form. Details: This is a test sent from the new bug reporting form at http://www.postgresql.org/bugform.php. I'd appreciate an email from someone to let me know that this made it onto the bugs list OK as I'm not currently subscribed. Cheers, Dave. Got it. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Backslash Bug in ARE Class-Shorthand Escape?
David Fetter wrote: I have a little puzzlement. In the first select, I double the backslash and return true. In the second, I don't and get false. Have I missed something important in the docs? I don't know if it is clear in the docs anywhere wrt regex, but the string literal parser will consume one layer of backslashes on you. So in your first case '\\d' is fed into the regex matching function as '\d' (string literal parser sees \\ == escape \ == \), and in the second case '\d' is fed in as 'd' (string literal parser sees \d == escape d == d). The basic rule at work here is you need to double up all backslashes. HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Backslash Bug in ARE Class-Shorthand Escape?
Joe Conway wrote: David Fetter wrote: I have a little puzzlement. In the first select, I double the backslash and return true. In the second, I don't and get false. Have I missed something important in the docs? I don't know if it is clear in the docs anywhere wrt regex, but the string literal parser will consume one layer of backslashes on you. So in your first case '\\d' is fed into the regex matching function as '\d' (string literal parser sees \\ == escape \ == \), and in the second case '\d' is fed in as 'd' (string literal parser sees \d == escape d == d). The basic rule at work here is you need to double up all backslashes. As a follow-up, there is a statement to this effect in the section on LIKE, that applies, in part at least, to the regexes as well. See: http://www.postgresql.org/docs/current/static/functions-matching.html Specifically, about the 7th paragraph: "Note that the backslash already has a special meaning in string literals, so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement. Thus, writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character with ESCAPE; then a backslash is not special to LIKE anymore. (But it is still special to the string literal parser, so you still need two of them.)" Part of this should probably be pulled out of the section on LIKE and into the introduction for pattern matching in general. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Error compiling PostgreSQL 7.4 for Fedora Core 1
Tom Lane wrote: We could fall back in that direction if we had to. But I'd like to understand why we have to, first. Gen_fmgrtab.sh has worked on all our supported platforms for a long time, and I'm disinclined to assume that it's suddenly broken ... especially on what's presumably a modern platform. I'm having a real problem with the idea that Fedora incorporates a broken awk or sed. I was able to rebuild from source rpm on a Fedora machine without any problem, so I don't think it's a case of wholesale breakage. It's not my normal development machine, but I'll try compiling from source [...tries...] Yup, I got a clean build from source with the following configure line: ./configure \ --prefix=/usr/local/pgsql-7.4 \ --with-pgport=55434 \ --with-perl \ --with-tcl \ --with-tclconfig=/usr/lib \ --without-tk \ --with-python \ --enable-nls \ --enable-debug \ --enable-cassert \ --enable-depend \ --with-openssl \ --with-pam \ --enable-integer-datetimes \ --with-krb5=/usr/kerberos \ --with-includes=/usr/include/et/ The only adjustment from my RH9 box was the last line. Without it com_err.h wasn't being found. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1007: error during installation
PostgreSQL Bugs List wrote: This application has failed to start because mspdb71.dll was not found. Re-installing the application may fix this problem. This has nothing to do with Postgres. "mspdb71.dll" is part of Visual Studio .Net -- look in: "c:\Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE". More than likely you neglected to run vsvars32.bat to set up your environment correctly. HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] NULL in arrays
Tom Lane wrote: As we used to say at HP, this is not a bug, it's a definition disagreement. You need to give a coherent argument why we should change, not just claim it's wrong. Additionally, this behavior was discussed during the 7.4 development and beta cycles on at least a couple occassions -- that would have been the time to complain, not now. For example, see this thread during beta: http://archives.postgresql.org/pgsql-hackers/2003-07/msg00747.php Given the present lack of support for null elements in arrays, it's impossible to have any really pleasant behavior in cases like this. But I don't see an inherent reason why "raise an error" is better than "return a null array". In fact, the above referenced thread shows a scenario where the former behavior is unpleasant. I think Joe Conway is planning to tackle that underlying misfeature for 7.5. Whenever it happens, it will result in a number of behavioral changes for arrays. I'm not eager to move the definition around in the meantime, especially not in dot-releases. Agreed. This and a few other changes to bring us closer to SQL99/SQL2003 compliance (see this thread: http://archives.postgresql.org/pgsql-hackers/2003-06/msg01167.php ) will cause some reasonably significant behavioral changes. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] NULL in arrays
Dennis Bjorklund wrote: On Thu, 15 Jan 2004, Joe Conway wrote: Additionally, this behavior was discussed during the 7.4 development and beta cycles on at least a couple occassions -- that would have been the time to complain, not now. Well, I will complain whenever I see something I don't like :-) No issue with that, I just like to encourage more, ahem, timely feedback whenever possible ;-). In the documentation I got the impression that NULL values inside an array was not allowed. Now I know that you are allowed to form such an expression and that it evaluates to the NULL array. The documentation should be fixed then (or maybe it is documented buy i've not seen it). I agree, and see Tom does too in a nearby post. Do you want to propose some wording, or just leave it up to me? Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Glitch: cannot use Arrays with Raise Notice
Josh Berkus wrote: Bug: Cannot Use Arrays with Raise Notice in PL/pgSQL. Version Tested: 7.4.1 Severity: Annoyance Description: Attempting to pass an array element to Raise Notice in PL/pgSQL will produce a parse error: I can reproduce this with cvs tip -- I'll check into it. Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] casting strings to multidimensional arrays yields strange
Tom Lane wrote:
Right now I think the sanest behavior would be to throw an error on
non-rectangular input. Once we have support for null elements in
arrays, however, it would arguably be reasonable to pad with NULLs
where needed, so that the above would be read as
{{1,2},{2,3},{4,NULL}}
{{1,NULL},{2,3},{4,5}}
respectively. If that's the direction we want to head in, it would
probably be best to leave array_in alone until we can do that; users
tend to get unhappy when we change behavior repeatedly.
I think that even once we support NULL array elements, they should be
explicitly requested -- i.e. throwing an error on non-rectangular input
is still the right thing to do. I haven't suggested that in the past
because of the backward-compatibility issue, but maybe now is the time
to bite the bullet.
If you think this qualifies as a bug fix for 7.5, I can take a look at
it next week.
Joe
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] BUG #1313: problems with array syntax parsing in SQL
PostgreSQL Bugs List wrote:
In INSERT statements the string '{}' is correctly parsed as an empty array
when '{ }' is not, as if the space was signifiant, and generates a syntax
error.
Also
'{"A", "B"}' will be correctly parsed when
'{"A", "B" }' (note the space before the closing brace ) will generate the
following entry '{"A","B "} : the space(s) between the latest double quote
and the closing brace is/are added to the latest item of the array.
Fixed in cvs:
regression=# select version();
version
PostgreSQL 8.0.0beta4 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)
(1 row)
regression=# create table test(f1 text[]);
CREATE TABLE
regression=# insert into test values ('{"A", "B" }');
INSERT 155428 1
regression=# select * from test;
f1
---
{A,B}
(1 row)
regression=# insert into test values ('{ }');
INSERT 155429 1
regression=# select * from test;
f1
---
{A,B}
{}
(2 rows)
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1532: typecast problem between arrays of an int8
Tom Lane wrote: "Ezequiel Tolnay" <[EMAIL PROTECTED]> writes: I've created the cardnumber_t datatype, which is an int8, to provide implicit typecasting with varchar padding the result with zeroes. Conversions work as expected between int4, int8, cardnumber_t and varchar. They also work fine between int4[], int8[] and cardnumber_t[], but when an attempt is made to convert a cardnumber_t[] to a varchar[], the connection is dropped. What's going on here is that array_map thinks it can use fn_extra of the passed FmgrInfo for its own purposes. That means that if the function to be called tries to use fn_extra for *its* own purposes, we have a conflict that is going to lead to core dumps in most cases. In other words, array_map pretty much doesn't work for calling anything except built-in functions. I think the best solution to this is to require array_map's caller to provide the state storage array_map wants, instead of doing it locally. Both of the existing callers can easily incorporate array_map's state data into their own state structs. Joe, you have any better ideas? That certainly looks like the least invasive fix for 8.0.x and 7.4.x. I have thought before that we were overloading fn_extra a bit too much. Is there any merit in having more than one "extra" member in FmgrInfo going forward? Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #5583: dblink connection error
On 07/30/2010 07:04 AM, Piergiorgio Buongiovanni wrote: > we obtain the following error: > > ERROR: password is required > DETAIL: Non-superuser cannot connect if the server does not request a > password. > HINT: Target server's authentication method must be changed. > > > ** Errore ** > > ERROR: password is required > Stato SQL: 2F003 > Dettaglio: Non-superuser cannot connect if the server does not request a > password. > Suggerimento: Target server's authentication method must be changed. > > Could you explain how to solve this problem? This is *not* a bug -- please post to the correct list next time. > HINT: Target server's authentication method must be changed. Please see: http://www.postgresql.org/docs/8.4/interactive/auth-pg-hba-conf.html Specifically, as suggested by the hint: auth-method Also read: http://www.postgresql.org/docs/8.4/interactive/contrib-dblink-connect.html -and- http://www.postgresql.org/docs/8.4/interactive/contrib-dblink-connect-u.html HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [BUGS] PL/R Median Busts Commit (Postgres 9.1.6 + plr 8.3.0.13 on Ubuntu 12.10 64 bit)
On 01/24/2013 05:21 AM, Mark Kirkwood wrote: > I admit - it sounds unlikely. However a simple scenario (attached) gives > rise to: This is the wrong place for the bug report on PL/R I think, but I'll take a look. Joe > WARNING: AbortTransaction while in COMMIT state > PANIC: cannot abort transaction 880983, it was already committed > > Essentially we are doing: > > BEGIN; > DROP TABLE IF EXISTS tab0; > CREATE TEMP TABLE tab0 ( id INTEGER PRIMARY KEY, val TEXT); > INSERT INTO tab0 SELECT generate_series(1,1000),'xx'; > SELECT median(id) FROM tab0; > DROP TABLE IF EXISTS tab1; > CREATE TEMP TABLE tab1 ( id INTEGER PRIMARY KEY, val TEXT); > INSERT INTO tab1 SELECT generate_series(1,1000),'xx'; > DROP TABLE IF EXISTS tab2; > ... > DROP TABLE IF EXISTS tab99; > CREATE TEMP TABLE tab99 ( id INTEGER PRIMARY KEY, val TEXT); > INSERT INTO tab99 SELECT generate_series(1,1000),'xx'; > SELECT median(id) FROM tab99; > COMMIT; > > > We crash at commit. > > Changing median to something else (e.g avg or even a non PLR median > function) makes the error vanish, so its either PLR or the specific PLR > median function causing the grief. -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 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] PL/R Median Busts Commit (Postgres 9.1.6 + plr 8.3.0.13 on Ubuntu 12.10 64 bit)
On 01/24/2013 08:01 PM, Mark Kirkwood wrote: > Ah right - sorry, I did a quick look for a mail list on the plr web site > and didn't spot anything. No problem. It is plr-general on pgfoundry: http://pgfoundry.org/mail/?group_id=1000247 Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 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] PL/R Median Busts Commit (Postgres 9.1.6 + plr 8.3.0.13 on Ubuntu 12.10 64 bit)
On 01/24/2013 10:36 PM, Tom Lane wrote: > Mark Kirkwood writes: >> Doh! Yes of course, sorry for the noise. I was busy thinking that the >> issue could be tied up with sinval and plan caching (if there is any) in >> plr and got excited about seeing something in gdb...and didn't think >> carefully about why what I was seeing was not a bug at all :-( Thanks >> for clarifying! > > FWIW, presumably the failure comes from something throwing elog(ERROR) > after RecordTransactionCommit(). So what I'd try is setting a > breakpoint at errfinish and investigating what's causing the first error > call. Yeah -- will do. Thanks! Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 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] PL/R Median Busts Commit (Postgres 9.1.6 + plr 8.3.0.13 on Ubuntu 12.10 64 bit)
On 01/28/2013 03:57 PM, Mark Kirkwood wrote: > Like a dog with a bone - sometimes it's hard to let go... given the > quite readable nature of the R source I managed to find what I *think* > is the "don't do R signals switch" (which I'd obviously missed before). > Making use of this seems to fix the original bug - and possibly the > SIGINT stealing too. > > Patch attached to set the variable (R_SignalHandlers = 0), and remove > the SIGINT workaround. Cool -- thanks. Maybe that got added in a release since last I looked. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 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] tablefunc extension
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/19/2013 12:24 PM, Carl Clemens wrote: > The following query appears to be correct but fails to execute. Your example is incorrect, however it is not clear (to me at least) from your example what you are expecting as output -- please see the documentation: http://www.postgresql.org/docs/9.2/interactive/tablefunc.html If you still have a problem please provide SQL that can be cut and pasted which: 1) create your table 2) insert sample rows Then show us what out put you are trying to acheive as output from that data. Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJSP3qGAAoJEDfy90M199hljPoP/R/LPHwlkQYtIE9YjNS1FRoJ viivrvUzAeP27zivgo8rIIrfXNyghUTfE8dyBRL1JB934PLcyu83+J9fcfW979xO ogt2J4JJ3pctfOOm0cMdlYDarZPeo4VGXFQMe1K/7qMSZJGLUWbGwlR8pF2o/AGa JdIMxwWuv6DwmU7yt8YnwWYRuyScUPyBFJCqntcrlfIDDlB313fdSUpu55c5/c89 Nq6+OGWScN9hA5AGm6AeucGEs8g3aNuevKN+AA6BN0D1wuo+pvkdK+iI9MlNyYlD cB+i7+oluSb+UkNNcvz+PW0/3D0CJ12/nZfhOFsJCoplYzbuPJbZfI9eA/d9r8E3 YdDbN1/ofVAHLKx8Wy9LKqsGDb0pLFCTxc+jZhRkR87qf9qJ1OAb/DJHj7b9A0EC /xy5LoxAFF61yGZjK9dPllF/asJcJkm3074G+hfCG5NdlJdOzshQ6eSNZqwOSTOM 6TPfQNTkpGmFfuuKRYTQjjAC+rUO6rfD+OIA+02rhYuRA1d7bxQw/tZvhOA9ZwvT GVfpKHNnbG72euY8q9NMuamoImy1ivVdXkt1kkKKQkXi4UGwyRhS08Rf+JVfP5Zz rNNtFp8lAo/eo5C3P5vFGUMr/4hcvjnPV71ZjgN4dG4Gp/HWdUsXn09TUrr1xV27 gcDhB2QFu+vbuP4yG/+N =8/9U -END PGP SIGNATURE- -- 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] tablefunc extension
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/22/2013 04:17 PM, Joe Conway wrote: > If you still have a problem please provide SQL that can be cut and > pasted which: 1) create your table 2) insert sample rows Then show > us what out put you are trying to acheive as output from that > data. Oh, and switch to the pgsql-general list, as there is no evidence of any bug here. Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJSP357AAoJEDfy90M199hlzyAP/1uVXwa9twDS6vB5EKiXbM6J FSrktrUzaQGA47onFZ6JKFjCSy4LIhaY7bev+VIW5m9hxVT5bNXe6uPqf6QT+pdK CevU7M29FUIB/ncF62aNYMIdjsM7yXqh+ppSQjqs2cs4jOWzn2BBxb1QCRezps2K sHwOi5VfqKGcl/7Za/aX6ojzNfssgi9YGWF20c7IHhhhNAlXOlw6ARmnv98lsaS/ tVIUuzibVjAD36f0v80lx3+oOVv9H4IMDKfcRQ/c+V5RlGvo6ZxUiJYxRre5GAcw 1SllJ46kyGXxdhIWkPKBxF8/4E+QMc1rOqlLrUgj3j/1Ymua0YNlIL70M9tBcPIJ idN/z+2jXYa8vHkLtpcjXTZGEhSEppCFOOw1sYEznqaSkVARabGptUsiKdGG8pll D0FvKVzJRQeKWxdP0q5LwTF5BSz9KILT9vKPHcMz0aN9ddf25oDv9B5gyQECRxEf BQYz0nULvz3YPAAaEIQRZehg6HBR7jM82dAu4Hnpm9la50rtmandefr9PliUMlXB N3kkpnFQGvFOJpbOAvpSYsaJ2PxjPErlxe/0R5aJVMnpq/4E+1WDNAzm907sIsyb raPFMY/0gEeqjVsZ2+o/fARj3FT1jlSkAKumo8Td0Q82bdisPgY7Bk6RqB3BDhEv ZERlQeMkTiWESjB46W+f =LUHW -END PGP SIGNATURE- -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
