Re: [GENERAL] could not accept SSPI security context
thanks a lot for the hints. client side logging: the user name corresponds to the expected user, without the domain prefix (rsc). See the full log output below. security event log: I should get that shortly from our IT. Regards, Reto 29.11.2010 10:37:17 4412 Debug Entering NpgsqlConnection.NpgsqlConnection(NpgsqlConnection()) 29.11.2010 10:37:18 4412 Debug ConnectionString Option: HOST = ip 29.11.2010 10:37:18 4412 Debug ConnectionString Option: PORT = 5432 29.11.2010 10:37:18 4412 Debug ConnectionString Option: PROTOCOL = 3 29.11.2010 10:37:18 4412 Debug ConnectionString Option: DATABASE = some_db 29.11.2010 10:37:18 4412 Debug ConnectionString Option: USER ID = 29.11.2010 10:37:18 4412 Debug ConnectionString Option: PASSWORD = 29.11.2010 10:37:18 4412 Debug ConnectionString Option: SSL = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: SSLMODE = Disable 29.11.2010 10:37:18 4412 Debug ConnectionString Option: TIMEOUT = 15 29.11.2010 10:37:18 4412 Debug ConnectionString Option: SEARCHPATH = 29.11.2010 10:37:18 4412 Debug ConnectionString Option: POOLING = True 29.11.2010 10:37:18 4412 Debug ConnectionString Option: CONNECTIONLIFETIME = 15 29.11.2010 10:37:18 4412 Debug ConnectionString Option: MINPOOLSIZE = 1 29.11.2010 10:37:18 4412 Debug ConnectionString Option: MAXPOOLSIZE = 20 29.11.2010 10:37:18 4412 Debug ConnectionString Option: SYNCNOTIFICATION = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: COMMANDTIMEOUT = 20 29.11.2010 10:37:18 4412 Debug ConnectionString Option: ENLIST = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: PRELOADREADER = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: USEEXTENDEDTYPES = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: INTEGRATED SECURITY = true 29.11.2010 10:37:18 4412 Debug ConnectionString Option: COMPATIBLE = 2.0.11.0 29.11.2010 10:37:18 4412 Debug Entering NpgsqlConnection.Open() 29.11.2010 10:37:18 4412 Debug Get NpgsqlClosedState.Instance 29.11.2010 10:37:18 4412 Debug Get NpgsqlClosedState.Instance 29.11.2010 10:37:18 4412 Debug Entering NpgsqlClosedState.Open() 29.11.2010 10:37:19 4412 Debug Attempt to connect to 'ip'. 29.11.2010 10:37:19 4412 Normal Connected to: ip:5432. 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupPacket.NpgsqlStartupPacket() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupPacket.WriteToStream() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupPacket.WriteToStream_Ver_3() 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: user. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: rsc. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: database. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: some_db. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: DateStyle. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: ISO. 29.11.2010 10:37:19 4412 Debug Entering NpgsqlState.ProcessBackendResponses() 29.11.2010 10:37:19 4412 Debug AuthenticationRequest message received from server. 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupState.Authenticate() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlPasswordPacket.WriteToStream() 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: NTLMSSP ??( . 29.11.2010 10:37:19 4412 Debug AuthenticationRequest message received from server. 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupState.Authenticate() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlPasswordPacket.WriteToStream() 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: NTLMSSP t ? H ` f ? ?( T E S T . X Y Z - D E r s c T R I D E N T J?#0 ?n^V?1d1m?5???7O+ . 29.11.2010 10:37:21 4412 Debug Entering PGUtil.ReadString() 29.11.2010 10:37:21 4412 Debug Get NpgsqlEventLog.LogLevel 29.11.2010 10:37:21 4412 Debug String read: FATAL. 29.11.2010 10:37:21 4412 Debug Entering PGUtil.ReadString() 29.11.2010 10:37:21 4412 Debug Get NpgsqlEventLog.LogLevel 29.11.2010 10:37:21 4412 Debug String read: XX000. 29.11.2010 10:37:21 4412 Debug Entering PGUtil.ReadString() 29.11.2010 10:37:21 4412 Debug Get NpgsqlEventLog.LogLevel 29.11.2010 10:37:21 4412 Debug String read: could not accept SSPI security context. 29.11.2010 10:37:21 4412 Debug Entering PGUtil.ReadString() 29.11.2010 10:37:21 4412 Debug Get NpgsqlEventLog.LogLevel 29.11.2010 10:37:21 4412 Debug String read: The
Re: [GENERAL] plpyhton
On Sat, Nov 27, 2010 at 04:38:27PM +0530, c k wrote: How can we implement procedure cache? Cane you please give details regarding this? It will be helpful for me. This is something the PL implementation needs to do for you. I finally looked at the plpython code, and found that it does indeed do this for you. PLPython caches a procedure's input and return types, and a compiled representation of the procedure's source. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [GENERAL] ERROR: xlog flush request 17/4D6C2720 is not satisfied
2010/11/29 Sofer, Yuval yuval_so...@bmc.com: He reported about storage problems which triggered node switch (it is postgres installation on windows 2008 cluster) But now although postgres.exe is running, it is not really functioning… Sounds to me like your clustering software caused some data loss/corruption. Have you ever tested cluster failover before putting it in production? Did it cause this problem then? -- 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] could not accept SSPI security context
I just heard back from our IT. There's nothing in the logs for this connection attempt, but they noted in the Npgsql log that the authentication was attempted using NTLM. However our domain controller no longer supports NTLM, but only LDAP(s) and kerberos (it's a Windows 2008 server). From the docs I understand that with SSPI, pg should try kerberos first and fall back to NTLM. This works when connecting from psql. Maybe Npgsql goes straight for NTLM, at least when using it the way I do? 2010/11/29 Reto Schöning reto.schoen...@gmail.com thanks a lot for the hints. client side logging: the user name corresponds to the expected user, without the domain prefix (rsc). See the full log output below. security event log: I should get that shortly from our IT. Regards, Reto 29.11.2010 10:37:17 4412 Debug Entering NpgsqlConnection.NpgsqlConnection(NpgsqlConnection()) 29.11.2010 10:37:18 4412 Debug ConnectionString Option: HOST = ip 29.11.2010 10:37:18 4412 Debug ConnectionString Option: PORT = 5432 29.11.2010 10:37:18 4412 Debug ConnectionString Option: PROTOCOL = 3 29.11.2010 10:37:18 4412 Debug ConnectionString Option: DATABASE = some_db 29.11.2010 10:37:18 4412 Debug ConnectionString Option: USER ID = 29.11.2010 10:37:18 4412 Debug ConnectionString Option: PASSWORD = 29.11.2010 10:37:18 4412 Debug ConnectionString Option: SSL = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: SSLMODE = Disable 29.11.2010 10:37:18 4412 Debug ConnectionString Option: TIMEOUT = 15 29.11.2010 10:37:18 4412 Debug ConnectionString Option: SEARCHPATH = 29.11.2010 10:37:18 4412 Debug ConnectionString Option: POOLING = True 29.11.2010 10:37:18 4412 Debug ConnectionString Option: CONNECTIONLIFETIME = 15 29.11.2010 10:37:18 4412 Debug ConnectionString Option: MINPOOLSIZE = 1 29.11.2010 10:37:18 4412 Debug ConnectionString Option: MAXPOOLSIZE = 20 29.11.2010 10:37:18 4412 Debug ConnectionString Option: SYNCNOTIFICATION = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: COMMANDTIMEOUT = 20 29.11.2010 10:37:18 4412 Debug ConnectionString Option: ENLIST = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: PRELOADREADER = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: USEEXTENDEDTYPES = False 29.11.2010 10:37:18 4412 Debug ConnectionString Option: INTEGRATED SECURITY = true 29.11.2010 10:37:18 4412 Debug ConnectionString Option: COMPATIBLE = 2.0.11.0 29.11.2010 10:37:18 4412 Debug Entering NpgsqlConnection.Open() 29.11.2010 10:37:18 4412 Debug Get NpgsqlClosedState.Instance 29.11.2010 10:37:18 4412 Debug Get NpgsqlClosedState.Instance 29.11.2010 10:37:18 4412 Debug Entering NpgsqlClosedState.Open() 29.11.2010 10:37:19 4412 Debug Attempt to connect to 'ip'. 29.11.2010 10:37:19 4412 Normal Connected to: ip:5432. 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupPacket.NpgsqlStartupPacket() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupPacket.WriteToStream() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupPacket.WriteToStream_Ver_3() 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: user. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: rsc. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: database. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: some_db. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: DateStyle. 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: ISO. 29.11.2010 10:37:19 4412 Debug Entering NpgsqlState.ProcessBackendResponses() 29.11.2010 10:37:19 4412 Debug AuthenticationRequest message received from server. 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupState.Authenticate() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlPasswordPacket.WriteToStream() 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: NTLMSSP? ?( . 29.11.2010 10:37:19 4412 Debug AuthenticationRequest message received from server. 29.11.2010 10:37:19 4412 Debug Entering NpgsqlStartupState.Authenticate() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket() 29.11.2010 10:37:19 4412 Debug Entering NpgsqlPasswordPacket.WriteToStream() 29.11.2010 10:37:19 4412 Debug Entering PGUtil.WriteString() 29.11.2010 10:37:19 4412 Debug String written: NTLMSSPt ? H ` f ? ? ( T E S T . X Y Z - D E r s c T R I D E N T J?#0?n^ V?1d1m?5???7O+ . 29.11.2010 10:37:21 4412 Debug Entering PGUtil.ReadString() 29.11.2010 10:37:21 4412 Debug Get
[GENERAL] postgresql-8.4 error
I am running ubuntu 10.04 I have problem installing postgresql-8.4 when i tried to start postgresql /etc/init.d/postgresql-8.4 start I am getting this error * Starting PostgreSQL 8.4 database server * Error: could not exec /usr/lib/postgresql/8.4/bin/pg_ctl /usr/lib/postgresql/8.4/bin/pg_ctl start -D /var/lib/postgresql/8.4/main -l /var/log/postgresql/postgresql-8.4-main.log -s -o -c config_file=/etc/postgresql/8.4/main/postgresql.conf : -- Rashad
Re: [GENERAL] postgresql-8.4 error
On Monday 29 November 2010 7:02:46 am Mohammed Rashad wrote: I am running ubuntu 10.04 I have problem installing postgresql-8.4 when i tried to start postgresql /etc/init.d/postgresql-8.4 start I am getting this error * Starting PostgreSQL 8.4 database server * Error: could not exec /usr/lib/postgresql/8.4/bin/pg_ctl /usr/lib/postgresql/8.4/bin/pg_ctl start -D /var/lib/postgresql/8.4/main -l /var/log/postgresql/postgresql-8.4-main.log -s -o -c config_file=/etc/postgresql/8.4/main/postgresql.conf : Are you trying to start Postgres as root? If so Postgres will not run as root, you will need to start it as the Postgres superuser, usually that is postgres. -- 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] [BUGS] postgresql-8.4 error
Mohammed Rashad mohammedrasha...@gmail.com wrote: I am getting this error * Starting PostgreSQL 8.4 database server * Error: could not exec /usr/lib/postgresql/8.4/bin/pg_ctl /usr/lib/postgresql/8.4/bin/pg_ctl start -D /var/lib/postgresql/8.4/main -l /var/log/postgresql/postgresql-8.4-main.log -s -o -c config_file=/etc/postgresql/8.4/main/postgresql.conf : Anything in /var/log/postgresql/postgresql-8.4-main.log ? -Kevin -- 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] [BUGS] postgresql-8.4 error
I reinstalled the postgresql and got this error
* Starting PostgreSQL 8.4 database server
* Insecure directory in $ENV{PATH} while running with -T switch at
/usr/bin/pg_ctlcluster line 63.
On Mon, Nov 29, 2010 at 8:43 PM, Kevin Grittner kevin.gritt...@wicourts.gov
wrote:
Mohammed Rashad mohammedrasha...@gmail.com wrote:
I am getting this error
* Starting PostgreSQL 8.4 database server
* Error: could not exec
/usr/lib/postgresql/8.4/bin/pg_ctl
/usr/lib/postgresql/8.4/bin/pg_ctl start -D
/var/lib/postgresql/8.4/main -l
/var/log/postgresql/postgresql-8.4-main.log -s -o -c
config_file=/etc/postgresql/8.4/main/postgresql.conf :
Anything in /var/log/postgresql/postgresql-8.4-main.log ?
-Kevin
--
Rashad
Re: [BUGS] [GENERAL] postgresql-8.4 error
Adrian Klaver adrian.kla...@gmail.com wrote: On Monday 29 November 2010 7:02:46 am Mohammed Rashad wrote: /etc/init.d/postgresql-8.4 start Are you trying to start Postgres as root? If so Postgres will not run as root, you will need to start it as the Postgres superuser, usually that is postgres. Service scripts in /etc/init.d/ are normally run by root and su to the appropriate user for the service. -Kevin -- 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] [BUGS] postgresql-8.4 error
Hello Mohammed,
Mohammed Rashad [2010-11-29 20:50 +0530]:
I reinstalled the postgresql and got this error
* Starting PostgreSQL 8.4 database server
* Insecure directory in $ENV{PATH} while running with -T switch at
/usr/bin/pg_ctlcluster line 63.
This is not an upstream bug at all. Can you please open a Debian or
Ubuntu bug report, with the output of
ls -ld /sbin /bin /usr/sbin /usr/bin
? The error message suggests that one or more of these directories are
world-writable.
Thank you,
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
signature.asc
Description: Digital signature
Re: [GENERAL] postgresql-8.4 error
On Mon, Nov 29, 2010 at 8:02 AM, Mohammed Rashad mohammedrasha...@gmail.com wrote: I am running ubuntu 10.04 I have problem installing postgresql-8.4 when i tried to start postgresql /etc/init.d/postgresql-8.4 start I am getting this error * Starting PostgreSQL 8.4 database server * Error: could not exec /usr/lib/postgresql/8.4/bin/pg_ctl /usr/lib/postgresql/8.4/bin/pg_ctl start -D /var/lib/postgresql/8.4/main -l /var/log/postgresql/postgresql-8.4-main.log -s -o -c config_file=/etc/postgresql/8.4/main/postgresql.conf : I run ubuntu 10.04 and postgresql 8.4 just fine. Could you provide more info on how you've instaled 8.4? Was it through a normal apt-get install method? Or did you download some .deb packages somewhere and dpkg install them? Or compile by hand and using the service scripts that come with pgsql? The more you tell us how you've done things, the more we can help. -- 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] postgresql-8.4 error
As a followup I just noticed you cross posted to pgsql-bugs. 1: please don't cross post. 2: PLEASE don't cross post to bugs especially, unless you have a reproduceable test case that proves it's a pg bug. 3: Again, please don't cross post. -- 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] postgresql-8.4 error
You want to use apt-get purge to remove it completely. and keep the list cc'd others might have more input. On Mon, Nov 29, 2010 at 8:53 AM, Mohammed Rashad mohammedrasha...@gmail.com wrote: I installed using apt-get install postgresql-8.4 and it worked correctly for me today when i tried to start postgresql server using /etc/init.d/postgresql-8.4 restart it not starting now will you please tell me how to completely remove postgresql from my system and do a fresh install.? On Mon, Nov 29, 2010 at 9:20 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Nov 29, 2010 at 8:02 AM, Mohammed Rashad mohammedrasha...@gmail.com wrote: I am running ubuntu 10.04 I have problem installing postgresql-8.4 when i tried to start postgresql /etc/init.d/postgresql-8.4 start I am getting this error * Starting PostgreSQL 8.4 database server * Error: could not exec /usr/lib/postgresql/8.4/bin/pg_ctl /usr/lib/postgresql/8.4/bin/pg_ctl start -D /var/lib/postgresql/8.4/main -l /var/log/postgresql/postgresql-8.4-main.log -s -o -c config_file=/etc/postgresql/8.4/main/postgresql.conf : I run ubuntu 10.04 and postgresql 8.4 just fine. Could you provide more info on how you've instaled 8.4? Was it through a normal apt-get install method? Or did you download some .deb packages somewhere and dpkg install them? Or compile by hand and using the service scripts that come with pgsql? The more you tell us how you've done things, the more we can help. -- Rashad -- To understand recursion, one must first understand recursion. -- 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] postgresql-8.4 error
Dear scott, I forgot to add cc so you please ignore this I installed using apt-get install postgresql-8.4 and it worked correctly for me today when i tried to start postgresql server using /etc/init.d/postgresql-8.4 restart it not starting now will you please tell me how to completely remove postgresql from my system and do a fresh install.? On Mon, Nov 29, 2010 at 9:24 PM, Scott Marlowe scott.marl...@gmail.comwrote: You want to use apt-get purge to remove it completely. and keep the list cc'd others might have more input. On Mon, Nov 29, 2010 at 8:53 AM, Mohammed Rashad mohammedrasha...@gmail.com wrote: I installed using apt-get install postgresql-8.4 and it worked correctly for me today when i tried to start postgresql server using /etc/init.d/postgresql-8.4 restart it not starting now will you please tell me how to completely remove postgresql from my system and do a fresh install.? On Mon, Nov 29, 2010 at 9:20 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Nov 29, 2010 at 8:02 AM, Mohammed Rashad mohammedrasha...@gmail.com wrote: I am running ubuntu 10.04 I have problem installing postgresql-8.4 when i tried to start postgresql /etc/init.d/postgresql-8.4 start I am getting this error * Starting PostgreSQL 8.4 database server * Error: could not exec /usr/lib/postgresql/8.4/bin/pg_ctl /usr/lib/postgresql/8.4/bin/pg_ctl start -D /var/lib/postgresql/8.4/main -l /var/log/postgresql/postgresql-8.4-main.log -s -o -c config_file=/etc/postgresql/8.4/main/postgresql.conf : I run ubuntu 10.04 and postgresql 8.4 just fine. Could you provide more info on how you've instaled 8.4? Was it through a normal apt-get install method? Or did you download some .deb packages somewhere and dpkg install them? Or compile by hand and using the service scripts that come with pgsql? The more you tell us how you've done things, the more we can help. -- Rashad -- To understand recursion, one must first understand recursion. -- Rashad
[GENERAL] postgresql-8.4 error
i removed the postgresql packages using apt-get purge postgresql-8.4 and deleted all directories and installed using apt-get install postgresql-8.4 and get this error * Starting PostgreSQL 8.4 database server * Error: /var/lib/postgresql/8.4/main is not accessible or does not exist -- Rashad
[GENERAL] lock file permisson
when i start postgresql using /etc/init.d/postgresql-8.4 start I am getting this error IST FATAL: could not create lock file /var/run/postgresql/.s.PGSQL.5432.lock: Permission denied I changed ownership of /var/run/postgresql using chown postgres /var/run/postgresql -- Rashad
[GENERAL] current postgresql error
now i am getting this error when starting postgresql using /etc/init.d/postgresql-8.4 start in ubuntu 10.04 my data directory is copied from a different system. postgresql incorrect checksum in control file -- Rashad
Re: [GENERAL] Question about OID and TCID
Since in-place seems not possible, I am trying to use arrays to store different ranges of that column, and it's fast to fetch a big array, so I believe an array is stored continuously on disk. I can convert an array to a column easily by unnest() function, but I didn't find any function that converts a column to an array. Is there a efficient method to do that? Thanks - Original Message - From: Guillaume Lelarge guilla...@lelarge.info To: Zhipan Wang wzhi...@soe.ucsc.edu Cc: pgsql-general@postgresql.org Sent: Sunday, November 28, 2010 12:59:46 AM GMT -08:00 US/Canada Pacific Subject: Re: [GENERAL] Question about OID and TCID Le 27/11/2010 09:52, Zhipan Wang a écrit : Hi, I want to access part of a table on the disk sequentially, i,e., when I get to a tuple in the table, I need to read several pages of data in the table starting from this tuple. You shouldn't rely on the order on disk. It will change as soon as you update one. I guess CTID could be translated to physical address on the disk to retrieve this tuple, right? Yes, first number is the page number. The second one is the record number in that page. If so, how do I use CTID to retrieve a particular tuple (or a page) in SQL? For example, SELECT * FROM yourtable WHERE ctid='(0,5)'; Can I use OID to do this equally efficiently? No, they don't reflect the order on disk. Another question is: when I update a tuple in a table, this tuple will get a new CTID and it leaves a gap at the old CTID, and when I insert a new tuple, it's appended to the end of the table, so the gap is always there. Does this mean it actually inserts a new tuple and the out-dated tuple still occupies the space? Yes. Other sessions could still need to see the old tuple values. How can I write the updated tuple back to its original position to utilize disk space more efficiently? You don't need to. PostgreSQL will deal with that. As soon as you do a VACUUM, PostgreSQL will be able to use the dead space for the next UPDATE or INSERT query (if there is enough space of course). -- Guillaume http://www.postgresql.fr http://dalibo.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] Question about OID and TCID
On 29/11/2010 16:56, Zhipan Wang wrote: Since in-place seems not possible, I am trying to use arrays to store different ranges of that column, and it's fast to fetch a big array, so I believe an array is stored continuously on disk. I can convert an array to a column easily by unnest() function, but I didn't find any function that converts a column to an array. Is there a efficient method to do that? I think array_agg() is what you want. http://www.postgresql.org/docs/9.0/static/functions-aggregate.html 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
[GENERAL] PostgreSQL hanging on new connections?
hi,
got (another time, it's not the first thing) this situation:
1. server: 64gb of ram, 24 cores of xeon processors (hyperthreading is off),
storage is fusion io.
2. at one point load skyrocketed to 48 (i know, it's not high with this
hardware)
3. we were not able to connect to PostgreSQL from psql - psql when run
just froze.
4. postgresql: PostgreSQL 8.3.12 on x86_64-redhat-linux-gnu, compiled by
GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48)
What I did gather:
straced postmaster when the problem was happening, and I was opening new
connections. strace looks like this:
16:54:36.886720 select(6, [3 4 5], NULL, NULL, {54, 366000}) = 1 (in [4], left
{51, 563000})
16:54:39.768318 rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT
SYS RTMIN RT_1], NULL, 8) = 0
16:54:39.770795 accept(4, {sa_family=AF_INET, sin_port=htons(59661),
sin_addr=inet_addr(10.50.50.7)}, [16]) = 8
16:54:39.773895 getsockname(8, {sa_family=AF_INET, sin_port=htons(5432),
sin_addr=inet_addr(10.60.0.10)}, [16]) = 0
16:54:39.774522 setsockopt(8, SOL_TCP, TCP_NODELAY, [1], 4) = 0
16:54:39.774579 setsockopt(8, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
16:54:39.774729 clone(child_stack=0,
flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD,
child_tidptr=0x2b06874a4460) = 12891
16:54:39.854208 close(8)= 0
16:54:39.855800 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
16:54:39.856853 select(6, [3 4 5], NULL, NULL, {60, 0}) = 1 (in [4], left {57,
842000})
16:54:42.018570 rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT
SYS RTMIN RT_1], NULL, 8) = 0
16:54:42.027079 accept(4, {sa_family=AF_INET, sin_port=htons(59662),
sin_addr=inet_addr(10.50.50.7)}, [16]) = 8
16:54:42.046511 getsockname(8, {sa_family=AF_INET, sin_port=htons(5432),
sin_addr=inet_addr(10.60.0.10)}, [16]) = 0
16:54:42.095917 setsockopt(8, SOL_TCP, TCP_NODELAY, [1], 4) = 0
16:54:42.109190 setsockopt(8, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
16:54:42.111603 clone(child_stack=0,
flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD,
child_tidptr=0x2b06874a4460) = 12912
16:54:42.114379 close(8) = 0
16:54:42.120551 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
16:54:42.128922 select(6, [3 4 5], NULL, NULL, {60, 0}) = 1 (in [4], left {58,
593000})
so, everything looks right.
but - I was also stracing backends - just after creation (I did strace -ff -p
postmaster.pid -o x.log).
strace from single backend, just forked off:
16:54:39.852638 close(3)= 0
16:54:39.852823 close(4)= 0
16:54:39.852872 close(5)= 0
16:54:39.852910 close(6)= 0
16:54:39.852994 setsid()= 12891
16:54:39.853050 rt_sigaction(SIGTERM, {0x5ae490, [], SA_RESTORER|SA_RESTART,
0x3bd66302d0}, {0x588580, [], SA_RESTORER|SA_RESTART, 0x3bd66302d0}, 8) = 0
16:54:39.853169 rt_sigaction(SIGQUIT, {0x5ae490, [], SA_RESTORER|SA_RESTART,
0x3bd66302d0}, {0x588580, [], SA_RESTORER|SA_RESTART, 0x3bd66302d0}, 8) = 0
16:54:39.853215 rt_sigaction(SIGALRM, {0x5ae490, [], SA_RESTORER,
0x3bd66302d0}, {0x1, [], SA_RESTORER, 0x3bd66302d0}, 8) = 0
16:54:39.853259 rt_sigprocmask(SIG_SETMASK, ~[QUIT ILL TRAP ABRT BUS FPE SEGV
ALRM TERM CONT SYS RTMIN RT_1], NULL, 8) = 0
16:54:39.853504 write(2, \0\0^\0[2\0\0t2010-11-29 16:54:39 UTC [12891]: [1-1]
LOG: connection received: host=10.50.50.7 port=59661\n, 103) = 103
16:54:39.853622 setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={60, 0}},
NULL) = 0
16:54:39.853689 recvfrom(8, \0\0\0\10\4\322\26/, 8192, 0, NULL, NULL) = 8
16:54:39.853822 sendto(8, N, 1, 0, NULL, 0) = 1
16:54:39.853901 recvfrom(8,
\0\0\0%\0\3\0\0user\0web\0database\0x\0\0, 8192, 0, NULL, NULL) = 37
16:54:39.856007 setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={0, 0}},
NULL) = 0
16:54:39.856059 rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT
SYS RTMIN RT_1], NULL, 8) = 0
16:54:39.856130 write(2, \0\0r\0[2\0\0t2010-11-29 16:54:39 UTC
10.50.50.7(59661) [12891]: [2-1] LOG: connection authorized: user=web
database=x\n, 123) = 123
16:54:39.856255 rt_sigaction(SIGHUP, {0x5ad340, [], SA_RESTORER|SA_RESTART,
0x3bd66302d0}, {0x588a90, [], SA_RESTORER|SA_RESTART, 0x3bd66302d0}, 8) = 0
16:54:39.856315 rt_sigaction(SIGINT, {0x5ae350, [], SA_RESTORER|SA_RESTART,
0x3bd66302d0}, {0x588580, [], SA_RESTORER|SA_RESTART, 0x3bd66302d0}, 8) = 0
16:54:39.856359 rt_sigaction(SIGTERM, {0x5ae3f0, [], SA_RESTORER|SA_RESTART,
0x3bd66302d0}, {0x5ae490, [], SA_RESTORER|SA_RESTART, 0x3bd66302d0}, 8) = 0
16:54:39.856402 rt_sigaction(SIGQUIT, {0x5ae4a0, [], SA_RESTORER|SA_RESTART,
0x3bd66302d0}, {0x5ae490, [], SA_RESTORER|SA_RESTART, 0x3bd66302d0}, 8) = 0
16:54:39.856445 rt_sigaction(SIGALRM, {0x5a6160, [], SA_RESTORER,
0x3bd66302d0}, {0x5ae490, [], SA_RESTORER, 0x3bd66302d0}, 8) = 0
16:54:39.856484 rt_sigaction(SIGPIPE, {0x1, [], SA_RESTORER|SA_RESTART,
0x3bd66302d0}, {0x1, [], SA_RESTORER|SA_RESTART, 0x3bd66302d0}, 8) = 0
16:54:39.856526 rt_sigaction(SIGUSR1, {0x5a1740, [],
Re: [GENERAL] PostgreSQL hanging on new connections?
hubert depesz lubaczewski dep...@depesz.com writes: straced postmaster when the problem was happening, and I was opening new connections. strace looks like this: [ backend hangs on semop immediately after reading global/pg_database ] It looks like something had exclusive lock on the database that new connections wanted to connect to. AFAICS the only action in 8.3 that would do that would be a DROP DATABASE or RENAME DATABASE. What was that other session doing? 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] PostgreSQL hanging on new connections?
On Mon, Nov 29, 2010 at 01:23:00PM -0500, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: straced postmaster when the problem was happening, and I was opening new connections. strace looks like this: [ backend hangs on semop immediately after reading global/pg_database ] It looks like something had exclusive lock on the database that new connections wanted to connect to. AFAICS the only action in 8.3 that would do that would be a DROP DATABASE or RENAME DATABASE. What was that other session doing? just some select. as far as I know there are no backends with such commands - drop/rename db. depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] PostgreSQL hanging on new connections?
On Mon, Nov 29, 2010 at 01:23:00PM -0500, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: straced postmaster when the problem was happening, and I was opening new connections. strace looks like this: [ backend hangs on semop immediately after reading global/pg_database ] It looks like something had exclusive lock on the database that new connections wanted to connect to. AFAICS the only action in 8.3 that would do that would be a DROP DATABASE or RENAME DATABASE. What was that other session doing? verified in logs. there were no alter database or drop database statements. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] could not accept SSPI security context
On Mon, 29 Nov 2010 15:27:35 +0100, Reto Schöning reto.schoen...@gmail.com wrote: I just heard back from our IT. There's nothing in the logs for this connection attempt, but they noted in the Npgsql log that the authentication was attempted using NTLM. However our domain controller no longer supports NTLM, but only LDAP(s) and kerberos (it's a Windows 2008 server). From the docs I understand that with SSPI, pg should try kerberos first and fall back to NTLM. This works when connecting from psql. Maybe Npgsql goes straight for NTLM, at least when using it the way I do? Both are using the Negotiate SSP authentication package http://msdn.microsoft.com/en-us/library/aa378748%28v=VS.85%29.aspx Npgsql (SSPIHandler.cs): int status = AcquireCredentialsHandle( , negotiate, SECPKG_CRED_OUTBOUND, IntPtr.Zero, IntPtr.Zero, IntPtr.Zero, IntPtr.Zero, ref sspicred, out expire ); libpq (fe-auth.c): /* * Send initial SSPI authentication token. * If use_negotiate is 0, use kerberos authentication package which is * compatible with Unix. If use_negotiate is 1, use the negotiate package * which supports both kerberos and NTLM, but is not compatible with Unix. */ r = AcquireCredentialsHandle(NULL, use_negotiate ? negotiate : kerberos, SECPKG_CRED_OUTBOUND, NULL, NULL, NULL, NULL, conn-sspicred, expire); It should be a one line patch to force Npgsql into using kerberos but I can't see any reason why negotiate should act differently between Npgsql and libpq. Regards, Brar -- 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] current postgresql error
On 11/29/2010 08:35 AM, Mohammed Rashad wrote: now i am getting this error when starting postgresql using /etc/init.d/postgresql-8.4 start in ubuntu 10.04 my data directory is copied from a different system. postgresql incorrect checksum in control file -- Rashad As you found out doing the above may not work. The system you copy from has to be binary compatible with the one you are copying to. In order to get this problem and the others you mentioned solved, you will need to stick with a setup. At this point there are too many moving parts in your errors to get a handle on what is going on:) Also a step by step account of what you did to setup the system would be nice. -- 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] PostgreSQL hanging on new connections?
On Mon, Nov 29, 2010 at 1:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: hubert depesz lubaczewski dep...@depesz.com writes: straced postmaster when the problem was happening, and I was opening new connections. strace looks like this: [ backend hangs on semop immediately after reading global/pg_database ] It looks like something had exclusive lock on the database that new connections wanted to connect to. AFAICS the only action in 8.3 that would do that would be a DROP DATABASE or RENAME DATABASE. What was that other session doing? Every once in a while when I connect to my big DB it feels like it stalls the connection for a few seconds. I have no idea what causes it, but it does feel like it has to do with the load. The load is not all that high relative to what my box can handle. It doesn't happen often enough for me to track it down, though. It instinct is that it is waiting on a lock, but clearly there is no rename/drop happening on my main db else i'd be out of business :-) I too run 8.3 as primary right now... testing 9.0 for deployment soon-ish. -- 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] PostgreSQL hanging on new connections?
On Mon, 2010-11-29 at 15:57 -0500, Vick Khera wrote: On Mon, Nov 29, 2010 at 1:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: hubert depesz lubaczewski dep...@depesz.com writes: straced postmaster when the problem was happening, and I was opening new connections. strace looks like this: [ backend hangs on semop immediately after reading global/pg_database ] It looks like something had exclusive lock on the database that new connections wanted to connect to. AFAICS the only action in 8.3 that would do that would be a DROP DATABASE or RENAME DATABASE. What was that other session doing? Every once in a while when I connect to my big DB it feels like it stalls the connection for a few seconds. I have no idea what causes it, but it does feel like it has to do with the load. The load is not all that high relative to what my box can handle. It doesn't happen often enough for me to track it down, though. It instinct is that it is waiting on a lock, but clearly there is no rename/drop happening on my main db else i'd be out of business :-) I too run 8.3 as primary right now... testing 9.0 for deployment soon-ish. Initiating the connection all the caches etc? Perhaps trying to allocate shared memory? JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] PostgreSQL hanging on new connections?
On Nov 29, 2010, at 12:57 PM, Vick Khera wrote: On Mon, Nov 29, 2010 at 1:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: hubert depesz lubaczewski dep...@depesz.com writes: straced postmaster when the problem was happening, and I was opening new connections. strace looks like this: [ backend hangs on semop immediately after reading global/pg_database ] It looks like something had exclusive lock on the database that new connections wanted to connect to. AFAICS the only action in 8.3 that would do that would be a DROP DATABASE or RENAME DATABASE. What was that other session doing? Every once in a while when I connect to my big DB it feels like it stalls the connection for a few seconds. I have no idea what causes it, but it does feel like it has to do with the load. The load is not all that high relative to what my box can handle. It doesn't happen often enough for me to track it down, though. It instinct is that it is waiting on a lock, but clearly there is no rename/drop happening on my main db else i'd be out of business :-) I too run 8.3 as primary right now... testing 9.0 for deployment soon-ish. We're having similar issues on 8.4.[245]... occasionally psql takes anywhere from a few to several dozen seconds to connect. I've been unsuccessfully trying to blame spikes in the OS run queue (we desperately need some connection pooling) but if it's something to do with locks I can't see in pg_locks, that would explain why I haven't been able to figure out what's going on yet -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pgadmin for Fedora 14?
Hi, Is there a Fedora 14 rpm for pgadmin that works out of the box for pg 8 and pg 9? Thanks, Jerry Trapped in Steve Jobs Reality Distortion Field -- 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] Pgadmin for Fedora 14?
On 11/29/10 1:17 PM, Jerry LeVan wrote: Hi, Is there a Fedora 14 rpm for pgadmin that works out of the box for pg 8 and pg 9? you mean, like these http://yum.pgrpms.org/9.0/fedora/fedora-14-i386/repoview/ ? -- 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] Pgadmin for Fedora 14?
On Mon, Nov 29, 2010 at 04:17:44PM -0500, Jerry LeVan wrote: Hi, Is there a Fedora 14 rpm for pgadmin that works out of the box for pg 8 and pg 9? To get pgAdmin3, do the following as root: yum install pgadmin3 Just so as to abate some confusion, the major releases of PostgreSQL are 9.0, 8.4, 8.3, etc., not 9 and 8 :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Pgadmin for Fedora 14?
On 11/29/10 1:29 PM, David Fetter wrote: On Mon, Nov 29, 2010 at 04:17:44PM -0500, Jerry LeVan wrote: Hi, Is there a Fedora 14 rpm for pgadmin that works out of the box for pg 8 and pg 9? To get pgAdmin3, do the following as root: yum install pgadmin3 Just so as to abate some confusion, the major releases of PostgreSQL are 9.0, 8.4, 8.3, etc., not 9 and 8 :) fwiw, on the yum repo, the 9.0 compatible version of pgadmin is...pgadmin3_90 currently, pgadmin3_90-1.12.1-1.$DIST.$ARCH.rpm -- 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] PostgreSQL hanging on new connections?
On Mon, Nov 29, 2010 at 10:03 PM, Ben Chobot be...@silentmedia.com wrote: On Nov 29, 2010, at 12:57 PM, Vick Khera wrote: On Mon, Nov 29, 2010 at 1:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: hubert depesz lubaczewski dep...@depesz.com writes: straced postmaster when the problem was happening, and I was opening new connections. strace looks like this: [ backend hangs on semop immediately after reading global/pg_database ] It looks like something had exclusive lock on the database that new connections wanted to connect to. AFAICS the only action in 8.3 that would do that would be a DROP DATABASE or RENAME DATABASE. What was that other session doing? Every once in a while when I connect to my big DB it feels like it stalls the connection for a few seconds. I have no idea what causes it, but it does feel like it has to do with the load. The load is not all that high relative to what my box can handle. It doesn't happen often enough for me to track it down, though. It instinct is that it is waiting on a lock, but clearly there is no rename/drop happening on my main db else i'd be out of business :-) I too run 8.3 as primary right now... testing 9.0 for deployment soon-ish. We're having similar issues on 8.4.[245]... occasionally psql takes anywhere from a few to several dozen seconds to connect. I've been unsuccessfully trying to blame spikes in the OS run queue (we desperately need some connection pooling) but if it's something to do with locks I can't see in pg_locks, that would explain why I haven't been able to figure out what's going on yet -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general This is normal. PostgreSQL needs to create new server process to handle your requested connection. Then it needs to allocate resources to that new connection. It initializes shared memory for that connection. That is the stall you are mentioning. If you are time sensitive on connection times, then use connection pooling software like pg-bouncer or pg-pool2. Pooling will keep your server instance hot and those lags won't happen.
