Re: [GENERAL] Understanding Datum
2011/3/24 Nick Raj nickrajj...@gmail.com:
If Datum contains only the value (not having type specific info.), then
Suppose i want to print the Datum V value (already defined in postgres)
then printf(%??, V);
Because V is assigned by PG_GETARG_POINTER(1);
I don't having the information of type Datum.
How to print the value of Datum in postgres?
you have to find a adequate out function and you have to call it. A
out functions transform a binary Datum value to CString value.
#include executor/spi.h /* this is what you need to
work with SPI */
#include utils/lsyscache.h
PG_MODULE_MAGIC;
extern Datum quote_literal(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(quote_literal);
Datum
quote_literal(PG_FUNCTION_ARGS)
{
text *result;
Oid valtype = get_fn_expr_argtype(fcinfo-flinfo, 0);
Datum value = PG_GETARG_DATUM(0);
Oid typoutput;
booltypIsVarlena;
StringInfoData buf;
if (!OidIsValid(valtype))
elog(ERROR, could not determine data type of input);
initStringInfo(buf);
appendStringInfoChar(buf, '\'');
getTypeOutputInfo(valtype, typoutput, typIsVarlena);
appendStringInfoString(buf, OidOutputFunctionCall(typoutput, value));
appendStringInfoChar(buf, '\'');
result = DatumGetTextP(DirectFunctionCall1(textin,
CStringGetDatum(buf.data)));
PG_RETURN_TEXT_P(result);
}
CREATE OR REPLACE FUNCTION quote_literal(anyelement)
RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION quote_literal(text)
RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
Regards
Pavel Stehule
p.s. this is simple variant of this function. For production usage is
using cache necessary.
On Thu, Mar 24, 2011 at 2:35 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Nick Raj nickrajj...@gmail.com writes:
In postgres, typedef uintptr_t Datum
Datum is getting value from PG_GETARG_POINTER(1);
But, now problem is how would i know the type of PG_GETARG_POINTER(1)
(postgres internally pass this argument) to figure out datum type?
Datum does not carry any type information, only a value. Functions are
typically coded to know their input types a priori. If you want to
write code that is not type-specific then you'd better be passing around
type OIDs as well as values.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database recovery.
My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. -- Thanks Best Regards, Waqar Azeem
Re: [GENERAL] Database recovery.
On 03/24/11 12:07 AM, Waqar Azeem wrote: My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. you need everything in the $PGDATA directory. On my system here, its D:\Postgres\8.4\data but of course, your mileage may vary. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding Datum
On Thu, 24 Mar 2011 10:50:32 +0530, Nick Raj wrote: If Datum contains only the value (not having type specific info.), then Suppose i want to print the Datum V value (already defined in postgres) then printf(%??, V); Because V is assigned by PG_GETARG_POINTER(1); I dont having the information of type Datum. How to print the value of Datum in postgres? On Thu, Mar 24, 2011 at 2:35 AM, Tom Lane wrote: Nick Raj writes: In postgres, typedef uintptr_t Datum Datum is getting value from PG_GETARG_POINTER(1); But, now problem is how would i know the type of PG_GETARG_POINTER(1) (postgres internally pass this argument) to figure out datum type? Datum does not carry any type information, only a value. Functions are typically coded to know their input types a priori. If you want to write code that is not type-specific then youd better be passing around type OIDs as well as values. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org [2]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general [3] Links: -- [1] mailto:nickrajj...@gmail.com [2] mailto:pgsql-general@postgresql.org [3] http://www.postgresql.org/mailpref/pgsql-general [4] mailto:t...@sss.pgh.pa.us You may only find this from code context. Bear in mind, in simple words, datum is like void* with additional size header. If You write C function you now what kind of datum will income, and what should outcome (You declare this!). If you examine table, it's same situation, but You need ask system for this. Even if PG will have type bytes in Datum, PG supports custom types... If you wan't to operate on datums You can't work in a way take some datum and work on it. You need to obtain /context/, by getting what type tables has on column x, what type of results function will return, etc; You need to know from where Your datum income. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Deadlock in libpq
Hi, We're getting a deadlock in our application (a web application with a PostgreSQL backend) which I've traced to libpq. I've started our application in gdb, and when it hangs, I've inspected the backtraces. I've found a couple of threads I can account for (listening for new connections, background processes) and 77 threads waiting for a mutex lock: #0 0x7523d464 in __lll_lock_wait () from /lib/libpthread.so.0 #1 0x752385d9 in _L_lock_953 () from /lib/libpthread.so.0 #2 0x752383fb in pthread_mutex_lock () from /lib/libpthread.so.0 #3 0x76160650 in ?? () from /usr/lib/libpq.so.5 == pg_lockingcallback #4 0x7440b791 in ?? () from /lib/libcrypto.so.0.9.8 #5 0x7440bcc9 in ?? () from /lib/libcrypto.so.0.9.8 #6 0x747652fb in SSL_new () from /lib/libssl.so.0.9.8 #7 0x761604dc in ?? () from /usr/lib/libpq.so.5 == pqsecure_open_client #8 0x761525ce in PQconnectPoll () from /usr/lib/libpq.so.5 #9 0x76152f5e in ?? () from /usr/lib/libpq.so.5 == connectDBComplete #10 0x76153c5f in PQconnectdb () from /usr/lib/libpq.so.5 #11 0x00f9b518 in sccR_info () #12 0x in ?? () So it seems everything is waiting for a lock on a mutex from pq_lockarray (in fe-secure.c@846). Does anybody have any idea how this can happen? Is this something we're doing wrong (I hope so) or a bug in libpq? Some background: this happens only after a couple of thousand requests (each doing about 15 database calls), with occasional other requests coming in at the same time. Our server uses a Haskell binding to libpq (HDBC [1] and HDBC-postgresql [2]). Both client and server run on the same machine, running 64bit Ubuntu 10.04. The database version is PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit. I'm not sure how to determine the libpq version, but it is the most recent that comes with this ubuntu. The changelogs for Ubuntu suggest 8.4.7 as well. Connections are via TCP/IP to 127.0.0.1 with SSL turned on. The machine is under some CPU load when this happens. There is plenty of free memory. When I turned off SSL or connect via domain sockets, we got different errors that are possibly related: occasionally, the connection between client (our app) and server (database) is lost. On the client, we get: connectPostgreSQL: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. and on the server: could not send data to client: Broken pipe There is no further context around these messages. Any help would be greatly appreciated. Sincerely, -- Erik Hesselink http://silkapp.com [1] http://hackage.haskell.org/package/HDBC [2] http://hackage.haskell.org/package/HDBC-postgresql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DO Statement Body Parameters
On Thu, Mar 24, 2011 at 1:33 AM, David Johnston pol...@yahoo.com wrote: I am not sure how I'd go about testing a parameterized query without using JDBC... How about this? http://www.postgresql.org/docs/9.0/interactive/sql-prepare.html HTH, WBL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
[GENERAL] Strange loss of data during INSERT
Hi everybody, i'm having a very strange Problem: My JEE Application initially persists Users at startup. The problem is that the passwords are not stored, while other data with the same datatype on this table are stored correctly!?! If I turn log_statement to 'all' in config I can see the INSERT statement including the correct password(!!!) in log file, but if I take a look with pgAdmin all password fields are empty... When I try to execute the insert statement in pgAdmin it works fine. By the way: If I switch the DB to OracleXE, it works fine (But actually I don't want to user Oracle... ) Thanks for any ideas Sebastian Altmann ISO Software Systeme GmbH Eichendorffstrasse 29 90491 Nürnberg Tel.: +49/(911) - 99594-0 Fax: +49/(911) - 99594-129 mailto:sebastian.altm...@isogmbh.de http://www.isogmbh.de Amtsgericht Nürnberg HRB 18299 Geschäftsführer: Harald Goeb Sitz: Nürnberg
Re: [GENERAL] Strange loss of data during INSERT
Are you sure the password is NULL? Some fields are not shown in pgAdmin if they are too long, but that seems improbable to me for passwords. pgAdmin also has an option Show NULL values as NULL (in file|options|query). If you take the SQL from the log, can you reproduce this in postgres? If so, please email the code (i hope it's work with personal data scrambled ?). cheers, WBL On Thu, Mar 24, 2011 at 12:01 PM, Altmann, Sebastian sebastian.altm...@isogmbh.de wrote: Hi everybody, i’m having a very strange Problem: My JEE Application initially persists Users at startup. The problem is that the passwords are not stored, while other data with the same datatype on this table are stored correctly!?! If I turn log_statement to ‘all’ in config I can see the INSERT statement including the correct password(!!!) in log file, but if I take a look with pgAdmin all password fields are empty… When I try to execute the insert statement in pgAdmin it works fine. By the way: If I switch the DB to OracleXE, it works fine (But actually I don’t want to user Oracle… ) Thanks for any ideas Sebastian Altmann ISO Software Systeme GmbH Eichendorffstrasse 29 90491 Nürnberg Tel.: +49/(911) - 99594-0 Fax: +49/(911) - 99594-129 mailto:sebastian.altm...@isogmbh.de http://www.isogmbh.de Amtsgericht Nürnberg HRB 18299 Geschäftsführer: Harald Goeb Sitz: Nürnberg -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
Re: [RMX:#] Re: [GENERAL] Strange loss of data during INSERT
Thanks for your answer. I'm not sure if the Password is NULL or a empty string, I will check this in the evening, I have no access no... If I run the INSERT statement from pgAdmin it works fine! Here is the statement from the log file (with original personal data, after this are just my dummy entries for development): BEGIN insert into player (birthdate, eMail, firstname, lastname, password, username, id) values ($1, $2, $3, $4, $5, $6, $7) Parameter: $1 = '3885-08-14 00:00:00', $2 = 'a...@isogmbh.de', $3 = 'Sebastian', $4 = 'Altmann', $5 = 'alt.iso', $6 = 'alt', $7 = '54' COMMIT Sebastian Altmann ISO Software Systeme GmbH Eichendorffstrasse 29 90491 Nürnberg Tel.: +49/(911) - 99594-0 Fax: +49/(911) - 99594-129 mailto:sebastian.altm...@isogmbh.de http://www.isogmbh.de Amtsgericht Nürnberg HRB 18299 Geschäftsführer: Harald Goeb Sitz: Nürnberg Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Willy-Bas Loos Gesendet: Donnerstag, 24. März 2011 12:16 An: Altmann, Sebastian Cc: pgsql-general@postgresql.org Betreff: [RMX:#] Re: [GENERAL] Strange loss of data during INSERT Are you sure the password is NULL? Some fields are not shown in pgAdmin if they are too long, but that seems improbable to me for passwords. pgAdmin also has an option Show NULL values as NULL (in file|options|query). If you take the SQL from the log, can you reproduce this in postgres? If so, please email the code (i hope it's work with personal data scrambled ?). cheers, WBL On Thu, Mar 24, 2011 at 12:01 PM, Altmann, Sebastian sebastian.altm...@isogmbh.de wrote: Hi everybody, i'm having a very strange Problem: My JEE Application initially persists Users at startup. The problem is that the passwords are not stored, while other data with the same datatype on this table are stored correctly!?! If I turn log_statement to 'all' in config I can see the INSERT statement including the correct password(!!!) in log file, but if I take a look with pgAdmin all password fields are empty... When I try to execute the insert statement in pgAdmin it works fine. By the way: If I switch the DB to OracleXE, it works fine (But actually I don't want to user Oracle... ) Thanks for any ideas Sebastian Altmann ISO Software Systeme GmbH Eichendorffstrasse 29 90491 Nürnberg Tel.: +49/(911) - 99594-0 tel:%2B49%2F%28911%29%20-%2099594-0 Fax: +49/(911) - 99594-129 tel:%2B49%2F%28911%29%20-%2099594-129 mailto:sebastian.altm...@isogmbh.de http://www.isogmbh.de Amtsgericht Nürnberg HRB 18299 Geschäftsführer: Harald Goeb Sitz: Nürnberg -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
Re: [GENERAL] Database recovery.
I copy the whole folder 'Data' to another PC where fresh postgresql 8.4 is installed (same as default config. that previous version have had) when i started the server got following ... The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Press return to continue... any clue? On 03/24/11 12:07 AM, Waqar Azeem wrote: My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. you need everything in the $PGDATA directory. On my system here, itsD:\Postgres\8.4\data but of course, your mileage may vary. On Thu, Mar 24, 2011 at 12:07 PM, Waqar Azeem waqarazeem.priv...@gmail.comwrote: My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. -- Thanks Best Regards, Waqar Azeem -- Thanks Best Regards, Waqar Azeem
Re: [GENERAL] Database recovery.
On Thu, Mar 24, 2011 at 04:31:19PM +0500, Waqar Azeem wrote: The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Press return to continue... any clue? Hmmm. So, what's the output of running NET HELPMSG 3534 ? Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database recovery.
On 24/03/2011 11:31, Waqar Azeem wrote: I copy the whole folder'Data' to another PC where freshpostgresql8.4 is installed (same as default config. that previous version have had) when i started the server got following ... The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Pressreturn to continue... Have a look in the Postgres log - there's often (usually?) information there that doesn't get sent to the Windows event log. It's probably in $PGDATA/pg_log, unless you changed something in the logging configuration. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database recovery.
On Thu, Mar 24, 2011 at 12:37:25PM +0100, Karsten Hilbert wrote: More help is available by typing NET HELPMSG 3534. ... any clue? And the second Google result has this: http://archives.postgresql.org/pgsql-bugs/2009-05/msg5.php Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database recovery.
my windows event viewer says: 2011-03-24 11:24:22 GMT FATAL: could not create lock file postmaster.pid: Permission denied is this helpfull? On Thu, Mar 24, 2011 at 4:31 PM, Waqar Azeem waqarazeem.priv...@gmail.comwrote: I copy the whole folder 'Data' to another PC where fresh postgresql 8.4 is installed (same as default config. that previous version have had) when i started the server got following ... The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Press return to continue... any clue? On 03/24/11 12:07 AM, Waqar Azeem wrote: My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. you need everything in the $PGDATA directory. On my system here, itsD:\Postgres\8.4\data but of course, your mileage may vary. On Thu, Mar 24, 2011 at 12:07 PM, Waqar Azeem waqarazeem.priv...@gmail.com wrote: My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. -- Thanks Best Regards, Waqar Azeem -- Thanks Best Regards, Waqar Azeem -- Thanks Best Regards, Waqar Azeem
Re: [GENERAL] DO Statement Body Parameters
2011/3/24 Willy-Bas Loos willy...@gmail.com: On Thu, Mar 24, 2011 at 1:33 AM, David Johnston pol...@yahoo.com wrote: I am not sure how I'd go about testing a parameterized query without using JDBC... How about this? http://www.postgresql.org/docs/9.0/interactive/sql-prepare.html HTH, WBL you can't prepare a DO statement - it has no plan :(. I worked on parametrized DO statement, but this patch was rejected as premature optimalization. Regards Pavel Stehule -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database recovery.
Thanks Karsten n' Raymond, Finally, I change the logging in account to Local System account as suggested in your URL. And then change it back to .\postgres This magically worked for me. I think ctrl+alt+del was a compulsory part of XP trouble shootings :) -- Waqar On Thu, Mar 24, 2011 at 5:10 PM, Waqar Azeem waqarazeem.priv...@gmail.comwrote: my windows event viewer says: 2011-03-24 11:24:22 GMT FATAL: could not create lock file postmaster.pid: Permission denied is this helpfull? On Thu, Mar 24, 2011 at 4:31 PM, Waqar Azeem waqarazeem.priv...@gmail.com wrote: I copy the whole folder 'Data' to another PC where fresh postgresql 8.4 is installed (same as default config. that previous version have had) when i started the server got following ... The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Press return to continue... any clue? On 03/24/11 12:07 AM, Waqar Azeem wrote: My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. you need everything in the $PGDATA directory. On my system here, itsD:\Postgres\8.4\data but of course, your mileage may vary. On Thu, Mar 24, 2011 at 12:07 PM, Waqar Azeem waqarazeem.priv...@gmail.com wrote: My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. -- Thanks Best Regards, Waqar Azeem -- Thanks Best Regards, Waqar Azeem -- Thanks Best Regards, Waqar Azeem -- Thanks Best Regards, Waqar Azeem
[GENERAL] Problem calling setweight function from JDBC
Hello, I have a call to setweight function in a PreparedStatement with the following sql: update my_table set a_text_data=setweight(to_tsvector(? :: regconfig, ?), ? :: char) Later I set the parameters: stmt.setString(1, language); stmt.setString(2, textToIndex); stmt.setString(3, weight); But the call fails with: ERROR: function setweight(tsvector, character) does not exist I had very similar error with ts_vector where the cast to regconfig helped, but not in this case. Inspecting the setweight function in system catalogs shows that parameters are tsvector and char. What am I doing wrong? Thanks in advance, Viliam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database recovery.
Waqar, my windows event viewer says: 2011-03-24 11:24:22 GMT FATAL: could not create lock file postmaster.pid: Permission denied is this helpfull? yes. that says that the windows-user your PostgreSQL Service is running as has no write priv on the data directory. Check your file systems permissions and allow writing to the data directory for the user your PostgreSQL service runs as. Best wishes HArald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH GF: Harald Armin Massa Amtsgericht Stuttgart, HRB 736399 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] What does error psql: Kerberos 5 authentication not supported means?
Hi, I'm not quite sure but it probably means that postgresql was not built with kerberos 5 support. That would make sense if you are using a binary install (did not compile from source), because of the notice at http://www.postgresql.org/docs/9.0/interactive/auth-methods.html#KERBEROS-AUTH Note: Native Kerberos authentication has been deprecated and should be used only for backward compatibility. New and upgraded installations are encouraged to use the industry-standard GSSAPI authentication method (see Section 19.3.3) instead. hth, WBL On Wed, Mar 23, 2011 at 5:44 PM, yandong@emc.com wrote: Hi Guys, I tried to setup kerberos authentication with postgresql 8.4.7 on Ubuntu 10.10. When I use: $ psql –h ubuntu.server –U gavin dbname I get following error: psql: Kerberos 5 authentication not supported. And from the server side of postgresql, I could see following log: 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:17 PDT DEBUG: 0: exit(0) 2011-03-23 09:14:17 PDT LOCATION: proc_exit, ipc.c:135 2011-03-23 09:14:17 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to make 2011-03-23 09:14:17 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:17 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:17 PDT DEBUG: 0: reaping dead processes 2011-03-23 09:14:17 PDT LOCATION: reaper, postmaster.c:2243 2011-03-23 09:14:17 PDT DEBUG: 0: server process (PID 4592) exited with exit code 0 2011-03-23 09:14:17 PDT LOCATION: LogChildExit, postmaster.c:2725 2011-03-23 09:14:24 PDT LOG: 0: Kerberos recvauth returned error 103 2011-03-23 09:14:24 PDT LOCATION: pg_krb5_recvauth, auth.c:721 postgres: Software caused connection abort from krb5_recvauth 2011-03-23 09:14:24 PDT FATAL: 28000: Kerberos 5 authentication failed for user gavin 2011-03-23 09:14:24 PDT LOCATION: auth_failed, auth.c:273 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(1): 1 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:24 PDT DEBUG: 0: exit(1) 2011-03-23 09:14:24 PDT LOCATION: proc_exit, ipc.c:135 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:24 PDT DEBUG: 0: reaping dead processes What does this means and how could I get more verbose logs? Besides, is there any more howto/doc on integrate kerberos and postgresql besides the official documentation at http://www.postgresql.org/docs/9.0/interactive/client-authentication.html? Thanks very much in advance! Regards, Yandong -- Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
[GENERAL] Re: [ADMIN] What does error psql: Kerberos 5 authentication not supported means?
BTW, is there a way to find out what particular configure options were used for any given (binary) installation? WBL On Thu, Mar 24, 2011 at 2:11 PM, Willy-Bas Loos willy...@gmail.com wrote: Hi, I'm not quite sure but it probably means that postgresql was not built with kerberos 5 support. That would make sense if you are using a binary install (did not compile from source), because of the notice at http://www.postgresql.org/docs/9.0/interactive/auth-methods.html#KERBEROS-AUTH Note: Native Kerberos authentication has been deprecated and should be used only for backward compatibility. New and upgraded installations are encouraged to use the industry-standard GSSAPI authentication method (see Section 19.3.3) instead. hth, WBL On Wed, Mar 23, 2011 at 5:44 PM, yandong@emc.com wrote: Hi Guys, I tried to setup kerberos authentication with postgresql 8.4.7 on Ubuntu 10.10. When I use: $ psql –h ubuntu.server –U gavin dbname I get following error: psql: Kerberos 5 authentication not supported. And from the server side of postgresql, I could see following log: 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:17 PDT DEBUG: 0: exit(0) 2011-03-23 09:14:17 PDT LOCATION: proc_exit, ipc.c:135 2011-03-23 09:14:17 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to make 2011-03-23 09:14:17 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:17 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:17 PDT DEBUG: 0: reaping dead processes 2011-03-23 09:14:17 PDT LOCATION: reaper, postmaster.c:2243 2011-03-23 09:14:17 PDT DEBUG: 0: server process (PID 4592) exited with exit code 0 2011-03-23 09:14:17 PDT LOCATION: LogChildExit, postmaster.c:2725 2011-03-23 09:14:24 PDT LOG: 0: Kerberos recvauth returned error 103 2011-03-23 09:14:24 PDT LOCATION: pg_krb5_recvauth, auth.c:721 postgres: Software caused connection abort from krb5_recvauth 2011-03-23 09:14:24 PDT FATAL: 28000: Kerberos 5 authentication failed for user gavin 2011-03-23 09:14:24 PDT LOCATION: auth_failed, auth.c:273 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(1): 1 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:24 PDT DEBUG: 0: exit(1) 2011-03-23 09:14:24 PDT LOCATION: proc_exit, ipc.c:135 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:24 PDT DEBUG: 0: reaping dead processes What does this means and how could I get more verbose logs? Besides, is there any more howto/doc on integrate kerberos and postgresql besides the official documentation at http://www.postgresql.org/docs/9.0/interactive/client-authentication.html ? Thanks very much in advance! Regards, Yandong -- Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink hessel...@gmail.com wrote: Hi, We're getting a deadlock in our application (a web application with a PostgreSQL backend) which I've traced to libpq. I've started our application in gdb, and when it hangs, I've inspected the backtraces. I've found a couple of threads I can account for (listening for new connections, background processes) and 77 threads waiting for a mutex lock: #0 0x7523d464 in __lll_lock_wait () from /lib/libpthread.so.0 #1 0x752385d9 in _L_lock_953 () from /lib/libpthread.so.0 #2 0x752383fb in pthread_mutex_lock () from /lib/libpthread.so.0 #3 0x76160650 in ?? () from /usr/lib/libpq.so.5 == pg_lockingcallback #4 0x7440b791 in ?? () from /lib/libcrypto.so.0.9.8 #5 0x7440bcc9 in ?? () from /lib/libcrypto.so.0.9.8 #6 0x747652fb in SSL_new () from /lib/libssl.so.0.9.8 #7 0x761604dc in ?? () from /usr/lib/libpq.so.5 == pqsecure_open_client #8 0x761525ce in PQconnectPoll () from /usr/lib/libpq.so.5 #9 0x76152f5e in ?? () from /usr/lib/libpq.so.5 == connectDBComplete #10 0x76153c5f in PQconnectdb () from /usr/lib/libpq.so.5 #11 0x00f9b518 in sccR_info () #12 0x in ?? () So it seems everything is waiting for a lock on a mutex from pq_lockarray (in fe-secure.c@846). Does anybody have any idea how this can happen? Is this something we're doing wrong (I hope so) or a bug in libpq? Some background: this happens only after a couple of thousand requests (each doing about 15 database calls), with occasional other requests coming in at the same time. Our server uses a Haskell binding to libpq (HDBC [1] and HDBC-postgresql [2]). Both client and server run on the same machine, running 64bit Ubuntu 10.04. The database version is PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit. I'm not sure how to determine the libpq version, but it is the most recent that comes with this ubuntu. The changelogs for Ubuntu suggest 8.4.7 as well. Connections are via TCP/IP to 127.0.0.1 with SSL turned on. The machine is under some CPU load when this happens. There is plenty of free memory. When I turned off SSL or connect via domain sockets, we got different errors that are possibly related: occasionally, the connection between client (our app) and server (database) is lost. On the client, we get: connectPostgreSQL: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. and on the server: could not send data to client: Broken pipe There is no further context around these messages. Any help would be greatly appreciated. How did you initialize ssl? You are waiting inside a lock that is getting set up inside the crypto library. Unless you are having some type of library initialization issue, I'm suspicious the problem is really inside libpq. Is your application multithreaded, and if so are you properly synchronizing access to the connection object, etc? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [RMX:#] Re: [GENERAL] Strange loss of data during INSERT
On Thursday, March 24, 2011 4:26:30 am Altmann, Sebastian wrote: Thanks for your answer. I'm not sure if the Password is NULL or a empty string, I will check this in the evening, I have no access no... I thought you saw the passwords in the log file which would seem to indicate NOT NULL or not empty string. If I run the INSERT statement from pgAdmin it works fine! Are you sure your JEE app and pgAdmin are both pointing at the same database? Here is the statement from the log file (with original personal data, after this are just my dummy entries for development): BEGIN insert into player (birthdate, eMail, firstname, lastname, password, username, id) values ($1, $2, $3, $4, $5, $6, $7) Parameter: $1 = '3885-08-14 00:00:00', $2 = 'a...@isogmbh.de', $3 = 'Sebastian', $4 = 'Altmann', $5 = 'alt.iso', $6 = 'alt', $7 = '54' COMMIT Sebastian Altmann ISO Software Systeme GmbH Eichendorffstrasse 29 90491 Nürnberg Tel.: +49/(911) - 99594-0 Fax: +49/(911) - 99594-129 mailto:sebastian.altm...@isogmbh.de http://www.isogmbh.de Amtsgericht Nürnberg HRB 18299 Geschäftsführer: Harald Goeb Sitz: Nürnberg Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Willy-Bas Loos Gesendet: Donnerstag, 24. März 2011 12:16 An: Altmann, Sebastian Cc: pgsql-general@postgresql.org Betreff: [RMX:#] Re: [GENERAL] Strange loss of data during INSERT Are you sure the password is NULL? Some fields are not shown in pgAdmin if they are too long, but that seems improbable to me for passwords. pgAdmin also has an option Show NULL values as NULL (in file|options|query). If you take the SQL from the log, can you reproduce this in postgres? If so, please email the code (i hope it's work with personal data scrambled ?). cheers, WBL On Thu, Mar 24, 2011 at 12:01 PM, Altmann, Sebastian sebastian.altm...@isogmbh.de wrote: Hi everybody, i'm having a very strange Problem: My JEE Application initially persists Users at startup. The problem is that the passwords are not stored, while other data with the same datatype on this table are stored correctly!?! If I turn log_statement to 'all' in config I can see the INSERT statement including the correct password(!!!) in log file, but if I take a look with pgAdmin all password fields are empty... When I try to execute the insert statement in pgAdmin it works fine. By the way: If I switch the DB to OracleXE, it works fine (But actually I don't want to user Oracle... ) Thanks for any ideas Sebastian Altmann ISO Software Systeme GmbH Eichendorffstrasse 29 90491 Nürnberg Tel.: +49/(911) - 99594-0 tel:%2B49%2F%28911%29%20-%2099594-0 Fax: +49/(911) - 99594-129 tel:%2B49%2F%28911%29%20-%2099594-129 mailto:sebastian.altm...@isogmbh.de http://www.isogmbh.de Amtsgericht Nürnberg HRB 18299 Geschäftsführer: Harald Goeb Sitz: Nürnberg -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 14:23, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink hessel...@gmail.com wrote: Hi, We're getting a deadlock in our application (a web application with a PostgreSQL backend) which I've traced to libpq. I've started our application in gdb, and when it hangs, I've inspected the backtraces. I've found a couple of threads I can account for (listening for new connections, background processes) and 77 threads waiting for a mutex lock: #0 0x7523d464 in __lll_lock_wait () from /lib/libpthread.so.0 #1 0x752385d9 in _L_lock_953 () from /lib/libpthread.so.0 #2 0x752383fb in pthread_mutex_lock () from /lib/libpthread.so.0 #3 0x76160650 in ?? () from /usr/lib/libpq.so.5 == pg_lockingcallback #4 0x7440b791 in ?? () from /lib/libcrypto.so.0.9.8 #5 0x7440bcc9 in ?? () from /lib/libcrypto.so.0.9.8 #6 0x747652fb in SSL_new () from /lib/libssl.so.0.9.8 #7 0x761604dc in ?? () from /usr/lib/libpq.so.5 == pqsecure_open_client #8 0x761525ce in PQconnectPoll () from /usr/lib/libpq.so.5 #9 0x76152f5e in ?? () from /usr/lib/libpq.so.5 == connectDBComplete #10 0x76153c5f in PQconnectdb () from /usr/lib/libpq.so.5 #11 0x00f9b518 in sccR_info () #12 0x in ?? () So it seems everything is waiting for a lock on a mutex from pq_lockarray (in fe-secure.c@846). Does anybody have any idea how this can happen? Is this something we're doing wrong (I hope so) or a bug in libpq? Some background: this happens only after a couple of thousand requests (each doing about 15 database calls), with occasional other requests coming in at the same time. Our server uses a Haskell binding to libpq (HDBC [1] and HDBC-postgresql [2]). Both client and server run on the same machine, running 64bit Ubuntu 10.04. The database version is PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit. I'm not sure how to determine the libpq version, but it is the most recent that comes with this ubuntu. The changelogs for Ubuntu suggest 8.4.7 as well. Connections are via TCP/IP to 127.0.0.1 with SSL turned on. The machine is under some CPU load when this happens. There is plenty of free memory. When I turned off SSL or connect via domain sockets, we got different errors that are possibly related: occasionally, the connection between client (our app) and server (database) is lost. On the client, we get: connectPostgreSQL: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. and on the server: could not send data to client: Broken pipe There is no further context around these messages. Any help would be greatly appreciated. How did you initialize ssl? You are waiting inside a lock that is getting set up inside the crypto library. Unless you are having some type of library initialization issue, I'm suspicious the problem is really inside libpq. Is your application multithreaded, and if so are you properly synchronizing access to the connection object, etc? What do you mean exactly with How did you initialize ssl? I found [1], which I did not know about. This seems to be a very non-local problem: if one of our dependencies initializes ssl, and I use libpq as well, this will go wrong. I've done a quick look through all our dependencies, and none seem to use libcrypto or libssl. Our application is definitely multithreaded, as it is a web application. But every database transaction creates a new connection object. They are never shared between threads. The problem is very hard to reproduce. I've taken all queries that were performed when I last reproduced it, and have only those queries (and inserts/updates) running in two concurrent loops, but so far, that hasn't reproduced the problem yet. A couple of hours of running our application with a script performing requests against it can reproduce it, though. If this seems to be a problem inside libpq, should I create a bug report? I'm hesitant, as I don't have any steps to reproduce. -- Erik Hesselink http://silkapp.com [1] http://www.postgresql.org/docs/8.4/static/libpq-ssl.html#LIBPQ-SSL-INITIALIZE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 9:07 AM, Erik Hesselink hessel...@gmail.com wrote: On Thu, Mar 24, 2011 at 14:23, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink hessel...@gmail.com wrote: Hi, We're getting a deadlock in our application (a web application with a PostgreSQL backend) which I've traced to libpq. I've started our application in gdb, and when it hangs, I've inspected the backtraces. I've found a couple of threads I can account for (listening for new connections, background processes) and 77 threads waiting for a mutex lock: #0 0x7523d464 in __lll_lock_wait () from /lib/libpthread.so.0 #1 0x752385d9 in _L_lock_953 () from /lib/libpthread.so.0 #2 0x752383fb in pthread_mutex_lock () from /lib/libpthread.so.0 #3 0x76160650 in ?? () from /usr/lib/libpq.so.5 == pg_lockingcallback #4 0x7440b791 in ?? () from /lib/libcrypto.so.0.9.8 #5 0x7440bcc9 in ?? () from /lib/libcrypto.so.0.9.8 #6 0x747652fb in SSL_new () from /lib/libssl.so.0.9.8 #7 0x761604dc in ?? () from /usr/lib/libpq.so.5 == pqsecure_open_client #8 0x761525ce in PQconnectPoll () from /usr/lib/libpq.so.5 #9 0x76152f5e in ?? () from /usr/lib/libpq.so.5 == connectDBComplete #10 0x76153c5f in PQconnectdb () from /usr/lib/libpq.so.5 #11 0x00f9b518 in sccR_info () #12 0x in ?? () So it seems everything is waiting for a lock on a mutex from pq_lockarray (in fe-secure.c@846). Does anybody have any idea how this can happen? Is this something we're doing wrong (I hope so) or a bug in libpq? Some background: this happens only after a couple of thousand requests (each doing about 15 database calls), with occasional other requests coming in at the same time. Our server uses a Haskell binding to libpq (HDBC [1] and HDBC-postgresql [2]). Both client and server run on the same machine, running 64bit Ubuntu 10.04. The database version is PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit. I'm not sure how to determine the libpq version, but it is the most recent that comes with this ubuntu. The changelogs for Ubuntu suggest 8.4.7 as well. Connections are via TCP/IP to 127.0.0.1 with SSL turned on. The machine is under some CPU load when this happens. There is plenty of free memory. When I turned off SSL or connect via domain sockets, we got different errors that are possibly related: occasionally, the connection between client (our app) and server (database) is lost. On the client, we get: connectPostgreSQL: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. and on the server: could not send data to client: Broken pipe There is no further context around these messages. Any help would be greatly appreciated. How did you initialize ssl? You are waiting inside a lock that is getting set up inside the crypto library. Unless you are having some type of library initialization issue, I'm suspicious the problem is really inside libpq. Is your application multithreaded, and if so are you properly synchronizing access to the connection object, etc? What do you mean exactly with How did you initialize ssl? I found [1], which I did not know about. This seems to be a very non-local problem: if one of our dependencies initializes ssl, and I use libpq as well, this will go wrong. I've done a quick look through all our dependencies, and none seem to use libcrypto or libssl. *something* must be initializing ssl, or you can't make secure connections from libpq. you need to find out which pq ssl init function is begin called, when it is being called, and with what arguments. One of the main things PQInitSSL does is set up a lock vector which it passes to the crypto library. The fact you are having blocking issues around those locks is suggesting SSL was not set up properly, something happened after being set up so that the locks are no longer good, you have application thread issue (although that sounds unlikely), or (least likely worst case) there is a bug in crypto. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 15:21, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 24, 2011 at 9:07 AM, Erik Hesselink hessel...@gmail.com wrote: On Thu, Mar 24, 2011 at 14:23, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink hessel...@gmail.com wrote: Hi, We're getting a deadlock in our application (a web application with a PostgreSQL backend) which I've traced to libpq. I've started our application in gdb, and when it hangs, I've inspected the backtraces. I've found a couple of threads I can account for (listening for new connections, background processes) and 77 threads waiting for a mutex lock: #0 0x7523d464 in __lll_lock_wait () from /lib/libpthread.so.0 #1 0x752385d9 in _L_lock_953 () from /lib/libpthread.so.0 #2 0x752383fb in pthread_mutex_lock () from /lib/libpthread.so.0 #3 0x76160650 in ?? () from /usr/lib/libpq.so.5 == pg_lockingcallback #4 0x7440b791 in ?? () from /lib/libcrypto.so.0.9.8 #5 0x7440bcc9 in ?? () from /lib/libcrypto.so.0.9.8 #6 0x747652fb in SSL_new () from /lib/libssl.so.0.9.8 #7 0x761604dc in ?? () from /usr/lib/libpq.so.5 == pqsecure_open_client #8 0x761525ce in PQconnectPoll () from /usr/lib/libpq.so.5 #9 0x76152f5e in ?? () from /usr/lib/libpq.so.5 == connectDBComplete #10 0x76153c5f in PQconnectdb () from /usr/lib/libpq.so.5 #11 0x00f9b518 in sccR_info () #12 0x in ?? () So it seems everything is waiting for a lock on a mutex from pq_lockarray (in fe-secure.c@846). Does anybody have any idea how this can happen? Is this something we're doing wrong (I hope so) or a bug in libpq? Some background: this happens only after a couple of thousand requests (each doing about 15 database calls), with occasional other requests coming in at the same time. Our server uses a Haskell binding to libpq (HDBC [1] and HDBC-postgresql [2]). Both client and server run on the same machine, running 64bit Ubuntu 10.04. The database version is PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit. I'm not sure how to determine the libpq version, but it is the most recent that comes with this ubuntu. The changelogs for Ubuntu suggest 8.4.7 as well. Connections are via TCP/IP to 127.0.0.1 with SSL turned on. The machine is under some CPU load when this happens. There is plenty of free memory. When I turned off SSL or connect via domain sockets, we got different errors that are possibly related: occasionally, the connection between client (our app) and server (database) is lost. On the client, we get: connectPostgreSQL: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. and on the server: could not send data to client: Broken pipe There is no further context around these messages. Any help would be greatly appreciated. How did you initialize ssl? You are waiting inside a lock that is getting set up inside the crypto library. Unless you are having some type of library initialization issue, I'm suspicious the problem is really inside libpq. Is your application multithreaded, and if so are you properly synchronizing access to the connection object, etc? What do you mean exactly with How did you initialize ssl? I found [1], which I did not know about. This seems to be a very non-local problem: if one of our dependencies initializes ssl, and I use libpq as well, this will go wrong. I've done a quick look through all our dependencies, and none seem to use libcrypto or libssl. *something* must be initializing ssl, or you can't make secure connections from libpq. you need to find out which pq ssl init function is begin called, when it is being called, and with what arguments. One of the main things PQInitSSL does is set up a lock vector which it passes to the crypto library. The fact you are having blocking issues around those locks is suggesting SSL was not set up properly, something happened after being set up so that the locks are no longer good, you have application thread issue (although that sounds unlikely), or (least likely worst case) there is a bug in crypto. From the postgresql documentation I linked to in my last post, it seems that if I do not call PQinitOpenSSL and I do not initialize the libraries myself, libpq will do it for me. Is that correct? If so, then that is what is happening in my case. Regards, -- Erik Hesselink http://silkapp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] constraint partition issue
hyelluas helen_yell...@mcafee.com writes: You are right to the point, Tom. The datex is timestamp with time zone and the constraints were created as timestamp without time zone. As soon as I fixed that , it all started working. OK. In case anybody is wondering *why* that fixed it: a comparison between timestamp with time zone and timestamp without time zone isn't immutable, because it depends on the timezone runtime parameter to interpret the timestamp without time zone. So the planner can't rely on deductions about the comparison while making the plan --- they might not be true anymore by the time the plan is executed. Upshot is that the constraint exclusion machinery ignores these constraints. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
Merlin Moncure mmonc...@gmail.com writes: *something* must be initializing ssl, or you can't make secure connections from libpq. you need to find out which pq ssl init function is begin called, when it is being called, and with what arguments. One of the main things PQInitSSL does is set up a lock vector which it passes to the crypto library. The fact you are having blocking issues around those locks is suggesting SSL was not set up properly, something happened after being set up so that the locks are no longer good, you have application thread issue (although that sounds unlikely), or (least likely worst case) there is a bug in crypto. Given that Erik still sees trouble when not using SSL, my money would be on the idea that somewhere he's got two threads trying to use the same connection object concurrently. The SSL confusion is just one symptom of that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem calling setweight function from JDBC
=?UTF-8?B?VmlsaWFtIMSOdXJpbmE=?= viliam.dur...@kios.sk writes: I have a call to setweight function in a PreparedStatement with the following sql: update my_table set a_text_data=setweight(to_tsvector(? :: regconfig, ?), ? :: char) Make that ::char not ::char ... the quotes matter here, because char without quotes is a keyword. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [ADMIN] What does error psql: Kerberos 5 authentication not supported means?
Willy-Bas Loos willy...@gmail.com writes: BTW, is there a way to find out what particular configure options were used for any given (binary) installation? Run pg_config. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] What does error psql: Kerberos 5 authentication not supported means?
On Thu, Mar 24, 2011 at 6:49 PM, Willy-Bas Loos willy...@gmail.com wrote: BTW, is there a way to find out what particular configure options were used for any given (binary) installation? The *pg_config* utility prints configuration parameters of the currently installed version of PostgreSQL *--configure* Print the options that were given to the *configure* script when PostgreSQL was configured for building. This can be used to reproduce the identical configuration, or to find out with what options a binary package was built. --Raghu Ram WBL On Thu, Mar 24, 2011 at 2:11 PM, Willy-Bas Loos willy...@gmail.comwrote: Hi, I'm not quite sure but it probably means that postgresql was not built with kerberos 5 support. That would make sense if you are using a binary install (did not compile from source), because of the notice at http://www.postgresql.org/docs/9.0/interactive/auth-methods.html#KERBEROS-AUTH Note: Native Kerberos authentication has been deprecated and should be used only for backward compatibility. New and upgraded installations are encouraged to use the industry-standard GSSAPI authentication method (see Section 19.3.3) instead. hth, WBL On Wed, Mar 23, 2011 at 5:44 PM, yandong@emc.com wrote: Hi Guys, I tried to setup kerberos authentication with postgresql 8.4.7 on Ubuntu 10.10. When I use: $ psql –h ubuntu.server –U gavin dbname I get following error: psql: Kerberos 5 authentication not supported. And from the server side of postgresql, I could see following log: 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:17 PDT DEBUG: 0: exit(0) 2011-03-23 09:14:17 PDT LOCATION: proc_exit, ipc.c:135 2011-03-23 09:14:17 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to make 2011-03-23 09:14:17 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:17 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:17 PDT DEBUG: 0: reaping dead processes 2011-03-23 09:14:17 PDT LOCATION: reaper, postmaster.c:2243 2011-03-23 09:14:17 PDT DEBUG: 0: server process (PID 4592) exited with exit code 0 2011-03-23 09:14:17 PDT LOCATION: LogChildExit, postmaster.c:2725 2011-03-23 09:14:24 PDT LOG: 0: Kerberos recvauth returned error 103 2011-03-23 09:14:24 PDT LOCATION: pg_krb5_recvauth, auth.c:721 postgres: Software caused connection abort from krb5_recvauth 2011-03-23 09:14:24 PDT FATAL: 28000: Kerberos 5 authentication failed for user gavin 2011-03-23 09:14:24 PDT LOCATION: auth_failed, auth.c:273 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(1): 1 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:24 PDT DEBUG: 0: exit(1) 2011-03-23 09:14:24 PDT LOCATION: proc_exit, ipc.c:135 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:24 PDT DEBUG: 0: reaping dead processes What does this means and how could I get more verbose logs? Besides, is there any more howto/doc on integrate kerberos and postgresql besides the official documentation at http://www.postgresql.org/docs/9.0/interactive/client-authentication.html ? Thanks very much in advance! Regards, Yandong -- Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 16:00, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: *something* must be initializing ssl, or you can't make secure connections from libpq. you need to find out which pq ssl init function is begin called, when it is being called, and with what arguments. One of the main things PQInitSSL does is set up a lock vector which it passes to the crypto library. The fact you are having blocking issues around those locks is suggesting SSL was not set up properly, something happened after being set up so that the locks are no longer good, you have application thread issue (although that sounds unlikely), or (least likely worst case) there is a bug in crypto. Given that Erik still sees trouble when not using SSL, my money would be on the idea that somewhere he's got two threads trying to use the same connection object concurrently. The SSL confusion is just one symptom of that. I've just looked at all the spots in the code where I fork a new thread, and nowhere do I share a connection between threads. I've also checked the source for HDBC-postgresql, and it also creates a new connection each time I ask it to (i.e. no caching/pooling etc) by calling PQconnectdb. So I'm pretty sure I'm not sharing a connection between two threads. This is a web application, so there is pretty good isolation between different threads (almost no shared state). -- Erik Hesselink http://silkapp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 10:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: *something* must be initializing ssl, or you can't make secure connections from libpq. you need to find out which pq ssl init function is begin called, when it is being called, and with what arguments. One of the main things PQInitSSL does is set up a lock vector which it passes to the crypto library. The fact you are having blocking issues around those locks is suggesting SSL was not set up properly, something happened after being set up so that the locks are no longer good, you have application thread issue (although that sounds unlikely), or (least likely worst case) there is a bug in crypto. Given that Erik still sees trouble when not using SSL, my money would be on the idea that somewhere he's got two threads trying to use the same connection object concurrently. The SSL confusion is just one symptom of that. maybe -- but aside from the fact the OP is disclaiming that case, I'm not convinced you would see strictly 'broken pipe' error resulting from connection synchronization problems (surely, you'd see other problems eventually). Maybe one of his threads is dying and aggravating some other client side problem. Also, he's getting blocked in connection establishment. He needs to rule out the most obvious problem first -- PQInitSSL being called improperly or at the wrong time. OP: It's a library wide setting and must be called before the first connection is established and only once. Perhaps the HDBC driver has some bad logic there. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 16:43, Merlin Moncure mmonc...@gmail.com wrote: He needs to rule out the most obvious problem first -- PQInitSSL being called improperly or at the wrong time. OP: It's a library wide setting and must be called before the first connection is established and only once. Perhaps the HDBC driver has some bad logic there. I just grepped the source, and PQInitSSL doesn't appear once. So I think it is never called at all. I'll try adding it to our application startup code, and see if that fixes the problem. This doesn't explain the broken connections without SSL though. I think if my threads were dying, I'd see a log message: there are top level exception handlers that log to email, file and the console. The connection code also has a handler that closes the connection in case of an exception. -- Erik Hesselink http://silkapp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] which view is used another views
Hello, How can I determine the views which are using a certain view. Thanks in advance
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 10:54 AM, Erik Hesselink hessel...@gmail.com wrote: On Thu, Mar 24, 2011 at 16:43, Merlin Moncure mmonc...@gmail.com wrote: He needs to rule out the most obvious problem first -- PQInitSSL being called improperly or at the wrong time. OP: It's a library wide setting and must be called before the first connection is established and only once. Perhaps the HDBC driver has some bad logic there. I just grepped the source, and PQInitSSL doesn't appear once. So I think it is never called at all. I'll try adding it to our application startup code, and see if that fixes the problem. This doesn't explain the broken connections without SSL though. I think if my threads were dying, I'd see a log message: there are top level exception handlers that log to email, file and the console. The connection code also has a handler that closes the connection in case of an exception. Also check PQinitOpenSSL. For connections without SSL, all we know for sure is that the tcp connection is unexpectedly getting dropped. This could happen for all kinds of reasons -- like a miscreant router deciding the connection is idle too long (this could in fact be the issue aggravating the SSL lock issue, but that's worse and needs to be caught and extirpated). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 17:18, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 24, 2011 at 10:54 AM, Erik Hesselink hessel...@gmail.com wrote: On Thu, Mar 24, 2011 at 16:43, Merlin Moncure mmonc...@gmail.com wrote: He needs to rule out the most obvious problem first -- PQInitSSL being called improperly or at the wrong time. OP: It's a library wide setting and must be called before the first connection is established and only once. Perhaps the HDBC driver has some bad logic there. I just grepped the source, and PQInitSSL doesn't appear once. So I think it is never called at all. I'll try adding it to our application startup code, and see if that fixes the problem. This doesn't explain the broken connections without SSL though. I think if my threads were dying, I'd see a log message: there are top level exception handlers that log to email, file and the console. The connection code also has a handler that closes the connection in case of an exception. Also check PQinitOpenSSL. For connections without SSL, all we know for sure is that the tcp connection is unexpectedly getting dropped. This could happen for all kinds of reasons -- like a miscreant router deciding the connection is idle too long (this could in fact be the issue aggravating the SSL lock issue, but that's worse and needs to be caught and extirpated). PQinitOpenSSL also doesn't appear. I'll ask the HDBC maintainer if he's considered this issue. As far as connections getting dropped: yes, this sounds reasonable, but given that both the client and the server are running on the same machine, will connections (to 127.0.0.1) really be dropped once every 100.000 or so? -- Erik Hesselink http://silkapp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 11:27 AM, Erik Hesselink hessel...@gmail.com wrote: On Thu, Mar 24, 2011 at 17:18, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 24, 2011 at 10:54 AM, Erik Hesselink hessel...@gmail.com wrote: On Thu, Mar 24, 2011 at 16:43, Merlin Moncure mmonc...@gmail.com wrote: He needs to rule out the most obvious problem first -- PQInitSSL being called improperly or at the wrong time. OP: It's a library wide setting and must be called before the first connection is established and only once. Perhaps the HDBC driver has some bad logic there. I just grepped the source, and PQInitSSL doesn't appear once. So I think it is never called at all. I'll try adding it to our application startup code, and see if that fixes the problem. This doesn't explain the broken connections without SSL though. I think if my threads were dying, I'd see a log message: there are top level exception handlers that log to email, file and the console. The connection code also has a handler that closes the connection in case of an exception. Also check PQinitOpenSSL. For connections without SSL, all we know for sure is that the tcp connection is unexpectedly getting dropped. This could happen for all kinds of reasons -- like a miscreant router deciding the connection is idle too long (this could in fact be the issue aggravating the SSL lock issue, but that's worse and needs to be caught and extirpated). PQinitOpenSSL also doesn't appear. I'll ask the HDBC maintainer if he's considered this issue. As far as connections getting dropped: yes, this sounds reasonable, but given that both the client and the server are running on the same machine, will connections (to 127.0.0.1) really be dropped once every 100.000 or so? No, don't bother, I forgot the default behavior was to do both, which is probably correct in your case. InitSSL just signals if you want them to be done. libpq refcounts connections and does SSL initialization when connection count goes from 0-1 and destruction when it goes from 1-0. This operation is protected with mutex (you *are* using thread safe libpq, right?). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 11:52 AM, Merlin Moncure mmonc...@gmail.com wrote: As far as connections getting dropped: yes, this sounds reasonable, but given that both the client and the server are running on the same machine, will connections (to 127.0.0.1) really be dropped once every 100.000 or so? No, don't bother, I forgot the default behavior was to do both, which is probably correct in your case. InitSSL just signals if you want them to be done. libpq refcounts connections and does SSL initialization when connection count goes from 0-1 and destruction when it goes from 1-0. This operation is protected with mutex (you *are* using thread safe libpq, right?). meh, you have to be -- the locking stuff only gets set up w/thread safe libpq. It's basically impossible for that refcount to get thrown off aiui. hm. I'm going back to thinking tom was right and this is threading issue in the app...maybe there is something you haven't considered? merlin merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] which view is used another views
On 24 March 2011 18:06, salah jubeh s_ju...@yahoo.com wrote: Hello, How can I determine the views which are using a certain view. Name of the view can be searched on the view_definition column of the views view of the information schema.
Re: [GENERAL] which view is used another views
On 24/03/2011 16:06, salah jubeh wrote: Hello, How can I determine the views which are using a certain view. If you're using pgAdmin, there's a Dependants tab which shows you the objects depending on another object. Otherwise, I think you can query the pg_catalog.pg_depend table, but I don't know how you go about that. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [RMX:#] Re: [GENERAL] Strange loss of data during INSERT
WHAT THE HELL!?! Just came home from work and did another testing: I access my JEE App via EJBs and this is what i'm doing to create my users at startup: i call the LOCAL BeanInterface and the result is that my passwords get lost. By the way this is the same behavior when i rename password to city and put it e.g. from column 5 to 3 - city is not stored. But now the hammer: I can also call the REMOTE Interface of my bean from another program when my server has started, and BAM! password, city or whatever is stored properly! For the ones who do not now EJB: the Implementation is exactly the same for my remote and local interface, i debuged and saw that the passwords are there, i looked into the postgre logfile and saw the insert statement with pw value is ok, ... This is really ill sh... but i can live with that, as far as i know and it works when i call the createPlayer() method properly from remote interface when server has started. Next thing i will try is to use EJB remote interface internally which also works, but is slower Thanks for everybodies help Sebastian Altmann ISO Software Systeme GmbH Eichendorffstrasse 29 90491 Nürnberg Tel.: +49/(911) - 99594-0 Fax: +49/(911) - 99594-129 mailto:sebastian.altm...@isogmbh.de http://www.isogmbh.de Amtsgericht Nürnberg HRB 18299 Geschäftsführer: Harald Goeb Sitz: Nürnberg -Ursprüngliche Nachricht- Von: Adrian Klaver [mailto:adrian.kla...@gmail.com] Gesendet: Do 24.03.2011 15:01 An: pgsql-general@postgresql.org Cc: Altmann, Sebastian; Willy-Bas Loos Betreff: Re: [RMX:#] Re: [GENERAL] Strange loss of data during INSERT On Thursday, March 24, 2011 4:26:30 am Altmann, Sebastian wrote: Thanks for your answer. I'm not sure if the Password is NULL or a empty string, I will check this in the evening, I have no access no... I thought you saw the passwords in the log file which would seem to indicate NOT NULL or not empty string. If I run the INSERT statement from pgAdmin it works fine! Are you sure your JEE app and pgAdmin are both pointing at the same database? Here is the statement from the log file (with original personal data, after this are just my dummy entries for development): BEGIN insert into player (birthdate, eMail, firstname, lastname, password, username, id) values ($1, $2, $3, $4, $5, $6, $7) Parameter: $1 = '3885-08-14 00:00:00', $2 = 'a...@isogmbh.de', $3 = 'Sebastian', $4 = 'Altmann', $5 = 'alt.iso', $6 = 'alt', $7 = '54' COMMIT Sebastian Altmann ISO Software Systeme GmbH Eichendorffstrasse 29 90491 Nürnberg Tel.: +49/(911) - 99594-0 Fax: +49/(911) - 99594-129 mailto:sebastian.altm...@isogmbh.de http://www.isogmbh.de Amtsgericht Nürnberg HRB 18299 Geschäftsführer: Harald Goeb Sitz: Nürnberg Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Im Auftrag von Willy-Bas Loos Gesendet: Donnerstag, 24. März 2011 12:16 An: Altmann, Sebastian Cc: pgsql-general@postgresql.org Betreff: [RMX:#] Re: [GENERAL] Strange loss of data during INSERT Are you sure the password is NULL? Some fields are not shown in pgAdmin if they are too long, but that seems improbable to me for passwords. pgAdmin also has an option Show NULL values as NULL (in file|options|query). If you take the SQL from the log, can you reproduce this in postgres? If so, please email the code (i hope it's work with personal data scrambled ?). cheers, WBL On Thu, Mar 24, 2011 at 12:01 PM, Altmann, Sebastian sebastian.altm...@isogmbh.de wrote: Hi everybody, i'm having a very strange Problem: My JEE Application initially persists Users at startup. The problem is that the passwords are not stored, while other data with the same datatype on this table are stored correctly!?! If I turn log_statement to 'all' in config I can see the INSERT statement including the correct password(!!!) in log file, but if I take a look with pgAdmin all password fields are empty... When I try to execute the insert statement in pgAdmin it works fine. By the way: If I switch the DB to OracleXE, it works fine (But actually I don't want to user Oracle... ) Thanks for any ideas Sebastian Altmann ISO Software Systeme GmbH Eichendorffstrasse 29 90491 Nürnberg Tel.: +49/(911) - 99594-0 tel:%2B49%2F%28911%29%20-%2099594-0 Fax: +49/(911) - 99594-129 tel:%2B49%2F%28911%29%20-%2099594-129 mailto:sebastian.altm...@isogmbh.de http://www.isogmbh.de Amtsgericht Nürnberg HRB 18299 Geschäftsführer: Harald Goeb Sitz: Nürnberg -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] which view is used another views
Thanks guys pg_depend seems Useful, I will check the manual. Regards From: Raymond O'Donnell r...@iol.ie To: salah jubeh s_ju...@yahoo.com Cc: pgsql pgsql-general@postgresql.org Sent: Thu, March 24, 2011 6:18:16 PM Subject: Re: [GENERAL] which view is used another views On 24/03/2011 16:06, salah jubeh wrote: Hello, How can I determine the views which are using a certain view. If you're using pgAdmin, there's a Dependants tab which shows you the objects depending on another object. Otherwise, I think you can query the pg_catalog.pg_depend table, but I don't know how you go about that. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock in libpq
On Thu, Mar 24, 2011 at 11:57 AM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 24, 2011 at 11:52 AM, Merlin Moncure mmonc...@gmail.com wrote: As far as connections getting dropped: yes, this sounds reasonable, but given that both the client and the server are running on the same machine, will connections (to 127.0.0.1) really be dropped once every 100.000 or so? No, don't bother, I forgot the default behavior was to do both, which is probably correct in your case. InitSSL just signals if you want them to be done. libpq refcounts connections and does SSL initialization when connection count goes from 0-1 and destruction when it goes from 1-0. This operation is protected with mutex (you *are* using thread safe libpq, right?). meh, you have to be -- the locking stuff only gets set up w/thread safe libpq. It's basically impossible for that refcount to get thrown off aiui. hm. I'm going back to thinking tom was right and this is threading issue in the app...maybe there is something you haven't considered? hm, ISTM (I don't know haskell) that the hdbc driver isn't doing any type of synchronization at all unless it is using a non thread safe libpq...and in that case it uses a global mutex. That doesn't look correct -- the hdbc driver should be locking around the PGconn always, and globally if you're stuck with a non thread safe libpq. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Default permissions for CREATE SCHEMA/TABLE?
Any way I can have all newly created schemas/tables be owned by, or have all permissions granted to, a certain group, without having to remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE?
* Yang Zhang (yanghates...@gmail.com) wrote: Any way I can have all newly created schemas/tables be owned by, or have all permissions granted to, a certain group, without having to remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in advance. ALTER DEFAULT PRIVILEGES FOR ROLE my_user IN SCHEMA my_schema GRANT SELECT ON TABLES TO other_role; http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE?
On Thursday, March 24, 2011 1:52:24 pm Yang Zhang wrote: Any way I can have all newly created schemas/tables be owned by, or have all permissions granted to, a certain group, without having to remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in advance. If you are using 9.0: http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recursive function that receives a list of IDs and returns all child IDs
thanks a lot! that function does it exactly as wished ;)! Am 23.03.2011 22:46, schrieb Merlin Moncure: On Wed, Mar 23, 2011 at 10:29 AM, Sven Haagsven-h...@gmx.de wrote: hello pgsql fans out there, i've already created a function that returns a list of IDs of all sub-samples based on a given sample ID. this works fine. now i like to extend this function so that it can receive a list of sample IDs. e.g.: fn_get_subsamples(IN sample_numbers SETOF integer) here is the existing function: CREATE OR REPLACE FUNCTION fn_get_subsamples(IN sample_number integer) RETURNS SETOF integer AS $BODY$ WITH RECURSIVE recursetree(sample_number) AS ( SELECT sample_number FROM sample WHERE parent_sample = $1 UNION ALL SELECT t.sample_number FROM sample t JOIN recursetree rt ON rt.sample_number = t.parent_sample ) SELECT sample_number FROM recursetree; $BODY$ LANGUAGE sql VOLATILE CREATE OR REPLACE FUNCTION fn_get_subsamples(IN sample_numbers integer[]) RETURNS SETOF integer AS $BODY$ WITH RECURSIVE recursetree(sample_number) AS ( SELECT sample_number FROM sample WHERE parent_sample in (select unnest($1)) UNION ALL SELECT t.sample_number FROM sample t JOIN recursetree rt ON rt.sample_number = t.parent_sample ) SELECT sample_number FROM recursetree; $BODY$ LANGUAGE sql VOLATILE merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE?
Keep in mind if you want to alter the GLOBAL privileges (i.e., the defaults) granted via PUBLIC you MUST NOT specify a schema. From what I can tell there is no way to associate a default owner different that the one executing the CREATE statement (though some inheritance cases do arise IIRC). David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Stephen Frost Sent: Thursday, March 24, 2011 4:56 PM To: Yang Zhang Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE? * Yang Zhang (yanghates...@gmail.com) wrote: Any way I can have all newly created schemas/tables be owned by, or have all permissions granted to, a certain group, without having to remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in advance. ALTER DEFAULT PRIVILEGES FOR ROLE my_user IN SCHEMA my_schema GRANT SELECT ON TABLES TO other_role; http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html Thanks, Stephen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is Postgres 9 supported on Ubuntu Desktop10LTS?
I've been trying to install postgresql9 on my ubuntu Desktop10 machine and it seems that it is not supported, as the only version that is available in the software center is Pgsql 8.4? Having googled this, i cannot find a straight answer. Is Pgsql9 supported on Ubuntu10LTS? Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Is-Postgres-9-supported-on-Ubuntu-Desktop10LTS-tp4262935p4262935.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is Postgres 9 supported on Ubuntu Desktop10LTS?
On 25 March 2011 08:42, jcoder leid...@googlemail.com wrote: I've been trying to install postgresql9 on my ubuntu Desktop10 machine and it seems that it is not supported, as the only version that is available in the software center is Pgsql 8.4? Having googled this, i cannot find a straight answer. Is Pgsql9 supported on Ubuntu10LTS? Having googled this (I don't use Ubuntu, and compile postgres from source) I found this after about 1 minute ... https://launchpad.net/~pitti/+archive/postgresql Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is Postgres 9 supported on Ubuntu Desktop10LTS?
On Thursday, March 24, 2011 4:17:38 pm Andrej wrote: On 25 March 2011 08:42, jcoder leid...@googlemail.com wrote: I've been trying to install postgresql9 on my ubuntu Desktop10 machine and it seems that it is not supported, as the only version that is available in the software center is Pgsql 8.4? Having googled this, i cannot find a straight answer. Is Pgsql9 supported on Ubuntu10LTS? Having googled this (I don't use Ubuntu, and compile postgres from source) I found this after about 1 minute ... https://launchpad.net/~pitti/+archive/postgresql Cheers, Andrej This comes up often enough it would be nice to get that link included here: http://www.postgresql.org/download/linux#multibinary -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE?
Any luck if I'm still on 8.4? On Thu, Mar 24, 2011 at 3:17 PM, David Johnston pol...@yahoo.com wrote: Keep in mind if you want to alter the GLOBAL privileges (i.e., the defaults) granted via PUBLIC you MUST NOT specify a schema. From what I can tell there is no way to associate a default owner different that the one executing the CREATE statement (though some inheritance cases do arise IIRC). David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Stephen Frost Sent: Thursday, March 24, 2011 4:56 PM To: Yang Zhang Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE? * Yang Zhang (yanghates...@gmail.com) wrote: Any way I can have all newly created schemas/tables be owned by, or have all permissions granted to, a certain group, without having to remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in advance. ALTER DEFAULT PRIVILEGES FOR ROLE my_user IN SCHEMA my_schema GRANT SELECT ON TABLES TO other_role; http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html Thanks, Stephen -- Yang Zhang http://yz.mit.edu/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE?
On Thursday, March 24, 2011 6:10:32 pm Yang Zhang wrote: Any luck if I'm still on 8.4? As far as I know, no luck. If you search the list archives there are some scripts people have posted that will walk through a database or schema and change permissions. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] What does error psql: Kerberos 5 authentication not supported means?
Hi Willy and Raghu, I rebuild from source code with kerberos/gssapi support, and changed to use 'gss' authentication method, it works now :)! Thanks very much for your kindly help. Regards, Yandong From: raghu ram raghuchenn...@gmail.commailto:raghuchenn...@gmail.com Date: Thu, 24 Mar 2011 11:09:33 -0400 To: Willy-Bas Loos willy...@gmail.commailto:willy...@gmail.com Cc: bjcoe yandong@emc.commailto:yandong@emc.com, pgsql-ad...@postgresql.orgmailto:pgsql-ad...@postgresql.org pgsql-ad...@postgresql.orgmailto:pgsql-ad...@postgresql.org, pgsql-general@postgresql.orgmailto:pgsql-general@postgresql.org pgsql-general@postgresql.orgmailto:pgsql-general@postgresql.org Subject: Re: [ADMIN] What does error psql: Kerberos 5 authentication not supported means? On Thu, Mar 24, 2011 at 6:49 PM, Willy-Bas Loos willy...@gmail.commailto:willy...@gmail.com wrote: BTW, is there a way to find out what particular configure options were used for any given (binary) installation? The pg_config utility prints configuration parameters of the currently installed version of PostgreSQL --configure Print the options that were given to the configure script when PostgreSQL was configured for building. This can be used to reproduce the identical configuration, or to find out with what options a binary package was built. --Raghu Ram WBL On Thu, Mar 24, 2011 at 2:11 PM, Willy-Bas Loos willy...@gmail.commailto:willy...@gmail.com wrote: Hi, I'm not quite sure but it probably means that postgresql was not built with kerberos 5 support. That would make sense if you are using a binary install (did not compile from source), because of the notice at http://www.postgresql.org/docs/9.0/interactive/auth-methods.html#KERBEROS-AUTH Note: Native Kerberos authentication has been deprecated and should be used only for backward compatibility. New and upgraded installations are encouraged to use the industry-standard GSSAPI authentication method (see Section 19.3.3) instead. hth, WBL On Wed, Mar 23, 2011 at 5:44 PM, yandong@emc.commailto:yandong@emc.com wrote: Hi Guys, I tried to setup kerberos authentication with postgresql 8.4.7 on Ubuntu 10.10. When I use: $ psql –h ubuntu.server –U gavin dbname I get following error: psql: Kerberos 5 authentication not supported. And from the server side of postgresql, I could see following log: 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:17 PDT DEBUG: 0: exit(0) 2011-03-23 09:14:17 PDT LOCATION: proc_exit, ipc.c:135 2011-03-23 09:14:17 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to make 2011-03-23 09:14:17 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:17 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make 2011-03-23 09:14:17 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:17 PDT DEBUG: 0: reaping dead processes 2011-03-23 09:14:17 PDT LOCATION: reaper, postmaster.c:2243 2011-03-23 09:14:17 PDT DEBUG: 0: server process (PID 4592) exited with exit code 0 2011-03-23 09:14:17 PDT LOCATION: LogChildExit, postmaster.c:2725 2011-03-23 09:14:24 PDT LOG: 0: Kerberos recvauth returned error 103 2011-03-23 09:14:24 PDT LOCATION: pg_krb5_recvauth, auth.c:721 postgres: Software caused connection abort from krb5_recvauth 2011-03-23 09:14:24 PDT FATAL: 28000: Kerberos 5 authentication failed for user gavin 2011-03-23 09:14:24 PDT LOCATION: auth_failed, auth.c:273 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(1): 1 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:24 PDT DEBUG: 0: exit(1) 2011-03-23 09:14:24 PDT LOCATION: proc_exit, ipc.c:135 2011-03-23 09:14:24 PDT DEBUG: 0: shmem_exit(-1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: shmem_exit, ipc.c:211 2011-03-23 09:14:24 PDT DEBUG: 0: proc_exit(-1): 0 callbacks to make 2011-03-23 09:14:24 PDT LOCATION: proc_exit_prepare, ipc.c:183 2011-03-23 09:14:24 PDT DEBUG: 0: reaping dead processes What does this means and how could I get more verbose logs? Besides, is there any more howto/doc on integrate kerberos and postgresql besides the official documentation at http://www.postgresql.org/docs/9.0/interactive/client-authentication.html? Thanks very much in advance! Regards, Yandong -- Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.orgmailto:pgsql-ad...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes
[GENERAL] New Application Development Announcement
This AGPLv2 application will be designed to bring the control and data management capabilities of an RDBMS to the average user in a simple, easy to use OS independent application. The content will be delivered in an easy to use and manipulate format within the convenience of a modern browser. The initial focus of the application will be on cataloging a comic book collection but the ability to catalog other collections can and will be added at a later date as resources become available. If you want to catalog or track a specific collectionit all that will needed is a SQL table structure that follows simple guidlines to be discussed at a later time. Once completed, the framework and templates written in PHP/AJAX, will be capable of using interacting with this table sturcute with minimual changes. The application will use Postgres for the storage of the necessary data objects. Developers will interact with the data through the use of PHP, SQL, and AJAX. The user will be provided with a web application allowing the entering of values into the various tables, columns, and rows, with a minimal amount of typing, to track the data and organize it graphically so that they may be able to see the extent of or missing areas of, their collection. Advanced users and developers will be able to manipulate the structure of the database as well. Print, export, and import capabilities will be available in a format compatible with other programs including Microsoft Excel, Apple Numbers, Google Docs, or Open Office Calc as development continues. This will be available as a stand-alone application run from a hosted web server or the user will have the ability to install it on their machine through the use of a virtual appliance and access it locally or across a network. If they wish to build the server themselves the code base for the application and corrasponding website is also be available for download under the same license. There are other websites and programs that do these tasks quite well but none function as both an on-line service and a standalone program that share a common interface and have the ability to sync with each other and other public databases. While many other sites are low cost or free and provide other benefits such as a marketplace or forum, none provide the core collection service as both a downloadable pre-configured application stack and an on-line service with an Open Source license. The standalone programs provide good functionality but none are released under an open source license and most require a yearly fee to receive comic issue updates on top of the fee charged for an upgraded version of the initial software. Many of the interfaces to these programs are unnecessarily complicated as well, due to their feature lists. This application will have a minimalist approach to the user interface but without sacrificing the abilities inherent with a database or the design. The goal is to keep the interface as clean and simple as possible only allowing the average user as much control as the need, more advanced users or those that desire more features will be able to implement them on an as needed basis. For infomation on how to help, suggestions, or the current state of development you can check out the site http://collectablesdb.net or github.com/CollectablesDB Matt
[GENERAL] Npgsql 2.0.12 beta1 released!
Hi, all!! The Npgsql Development Team is proud to announce the Npgsql2 2.0.12 beta1 release! Npgsql is a .Net Data provider written 100% in C# which allows .net programs to talk to postgresql backends. Npgsql is licensed under BSD. More info can be obtained from http://www.npgsql.org Thsi is the release notes: Npgsql 2.0.12 beta1 (2.0.11.91) Important Notice: This is a beta release intended to test some critical fixes added since last stable release. Special attention should be given to connection pool interactions. We improved connection pool of Npgsql. Previous implementation was letting connections to be leaked. Now this implementation keeps a reference to all the connections and keeps better track of them. We also improved connection validation from pool before delivering it to the user. This may fix problems where users were reporting strange data from new connections. Another attention should be paid to Dataset and DataAdapter operations. We changed some internals of NpgsqlCommandBuilder which may cause problems. We'd like you to check if you get problems. For more information check this thread: http://pgfoundry.org/tracker/?func=detailatid=590aid=1010973group_id=1000140 Fixed bugs: [#1010951] AllowPartiallyTrustedCallers for npgsql assemblies. [#1010973] specified cast is invalid - datetime fields. In order to fix this bug I had to disable some code in NpgsqlCommandBuilder. Watch out for possible side effects. Fixed problems with SSPI authentication when the password had some non ascii characteres. Also, Npgsql supports to authenticate with an user name other than the current logged one. Thanks Ahmed Shinwari for tests and patch. [#1010997] Consider using System.Threading.Timer instead of System.Timers.Timer. Thanks Alex A Ermoshenko for feedback, patch and tests. NpgsqlConnector.CancelRequest leaks Connector. NpgsqlConnector.CancelRequest creates a connector to cancel the running request, but does not close it. More info at: http://pgfoundry.org/forum/forum.php?thread_id=10059forum_id=519 Thanks David Leaver for patch. Exception when closing connection. If your connection is in the Ready state and the stream is actually broken NpgsqlReadyState.Close will throw an exception when trying to write to the stream. More info can be found at: http://pgfoundry.org/forum/forum.php?thread_id=10066forum_id=519 Thanks David Leaver for the patch. In Multi-threaded applications there is a race condition that can occur which will result in a broken connection being re-added to the pool. Thanks David Leaver for patches. More information here: http://pgfoundry.org/forum/forum.php?thread_id=10067forum_id=519 [#1010986] Endless recursion on hanging connection in ProcessBackendResponsesEnum. Thanks Gildas for feedback and tests. Enhancements: Added performance patch to GetKeyName method. Thanks Dan Smith for patch. http://lists.pgfoundry.org/pipermail/npgsql-devel/2011-February/001117.html You can see full changelog and release notes here: http://pgfoundry.org/frs/shownotes.php?release_id=1727 You can dowload it from here: http://downloads.npgsql.org Thank you to Josh Cooley for all his help with entity framework support! Thank you to all who sent feedbacks, suggestions and patches. You helped to make this release. Thank you very much to David Leaver who gave a lot of feedback and patches to improve connection pool. Please, give it a try and feel free to let us know if you find any problem. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://fxjr.blogspot.com http://twitter.com/franciscojunior -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Out of memory
I've been getting database out of memory failures with some queries which deal with a reasonable amount of data. I was wondering what I should be looking at to stop this from happening. The typical messages I been getting are like this: http://pastebin.com/Jxfu3nYm The OS is: Linux TSTLHAPP01 2.6.32-29-server #58-Ubuntu SMP Fri Feb 11 21:06:51 UTC 2011 x86_64 GNU/Linux. It's a running on VMWare and, has 2 CPU's and 8GB of RAM. This VM is dedicated to PostgreSQL. The main OS parameters I have tuned are: vm.swappiness=0 vm.overcommit_memory=2 kernel.shmmax = 4196769792 kernel.shmall = 1024602 And the PostgreSQL is: PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit. The main changed psql parameters I've tuned are: shared_buffers = 2048MB maintenance_work_mem = 512MB work_mem = 200MB wal_buffers = 16MB effective_cache_size = 4094MB I have also try lowering the shared_buffers down to 1GB but it still ran out of memory. Cheers, Jeremy __ This message contains information, which is confidential and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank you. __
Re: [GENERAL] Out of memory
On Thu, Mar 24, 2011 at 9:23 PM, Jeremy Palmer jpal...@linz.govt.nz wrote: I’ve been getting database out of memory failures with some queries which deal with a reasonable amount of data. I was wondering what I should be looking at to stop this from happening. The typical messages I been getting are like this: http://pastebin.com/Jxfu3nYm The OS is: Linux TSTLHAPP01 2.6.32-29-server #58-Ubuntu SMP Fri Feb 11 21:06:51 UTC 2011 x86_64 GNU/Linux. It’s a running on VMWare and, has 2 CPU’s and 8GB of RAM. This VM is dedicated to PostgreSQL. The main OS parameters I have tuned are: work_mem = 200MB That's a really big work_mem. I have mainline db servers with 128G of ram that have work_mem set to 16M and that is still considered a little high in my book. If you drop work_mem down to 1MB does the out of memory go away? work_mem is how much memory EACH sort can use on its own, if you have a plpgsql procedure that keeps running query after query, it could use a LOT of memory really fast. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
