[GENERAL] Adding a non-null column without noticeable downtime
Hi all, I'm sure this has been answered somewhere, but I was not able to find anything in the list archives. I'm conceptually trying to do ALTER TABLE "foo" ADD COLUMN "bar" boolean NOT NULL DEFAULT False; without taking any noticeable downtime. I know I can divide the query up like so: ALTER TABLE "foo" ADD COLUMN "bar" boolean; UPDATE foo SET bar = False; -- Done in batches ALTER TABLE "foo" ALTER COLUMN "bar" SET DEFAULT False; ALTER TABLE "foo" ALTER COLUMN "bar" SET NOT NULL; The first 3 queries shouldn't impact other concurrent queries on the system. My question is about the sequential scan that occurs when setting the column NOT NULL. Will that sequential scan block other inserts or selects on the table? If so, can it be sped up by using an index (which would be created concurrently)? Thanks, Zev -- 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] plans for plpython in RDS?
On Tue, Feb 18, 2014 at 05:08:43PM -0800, Reece Hart wrote: > Does anyone know if there are plans to support plpython in Amazon's RDS? I > (approximately) understand the issue, but I don't know if there's any effort > to > remedy the problem or, rather, I shouldn't bother hoping. I think you would have to ask Amazon. If the problem is that plpython is not trustable, there is no way to make it trustable because of language limitations, so there might be no hope. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] GSSAPI/SSPI and mismatched user names
On Mon, Feb 24, 2014 at 1:06 PM, Stephen Frost wrote: > I'm afraid you're going to need to try harder to find out how to get the > Windows GSSAPI/SSPI code to give you the princ. I was actually pretty > sure that GSSAPI defined a way, but I don't know the Windows side of it > or if they decided to not bother implementing parts of GSSAPI. I don't think there's a Windows GSSAPI implementation at all. You're expected to go through Win32 and all of their security providers. Read here for some of the loveliness: http://stackoverflow.com/questions/7613468/getting-the-current-username-when-impersonated https://groups.google.com/forum/#!topic/microsoft.public.platformsdk.security/5L7ugO0Fc90 (Really, though, the Windows login infrastructure and API is rather nice.) > Exactly- this is not something we can solve with a little bit of > tweaking... Nuts. It sounded easy :P --Brian -- 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] GSSAPI/SSPI and mismatched user names
* Brian Crowell (br...@fluggo.com) wrote: > On Mon, Feb 24, 2014 at 12:55 PM, Stephen Frost wrote: > > * Brian Crowell (br...@fluggo.com) wrote: > >> https://github.com/npgsql/Npgsql/issues/162#issuecomment-35916650 > > > > Reading through this- can't you use GSSAPI to get the Kerberos princ > > found the ticket which is constructed? I'm pretty sure the MIT > > libraries support that, at least... > > I expected I might be able to do that on Linux, but right now I'm > trying to work out the Windows non-domain case. I'm afraid you're going to need to try harder to find out how to get the Windows GSSAPI/SSPI code to give you the princ. I was actually pretty sure that GSSAPI defined a way, but I don't know the Windows side of it or if they decided to not bother implementing parts of GSSAPI. > Unfortunately, in this case I don't even have a wrong-cased username > to start with. I have the user name of the logged-in non-domain user, > which is not the user name of the domain credentials I'm sending > across the network. You're going to need to figure out how to tell PG what PG user you want to log in as in the initial packet. > > We need the username to figure out which auth method we're using... > > Oh dear. Exactly- this is not something we can solve with a little bit of tweaking... Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] GSSAPI/SSPI and mismatched user names
On Mon, Feb 24, 2014 at 12:55 PM, Stephen Frost wrote: > * Brian Crowell (br...@fluggo.com) wrote: >> https://github.com/npgsql/Npgsql/issues/162#issuecomment-35916650 > > Reading through this- can't you use GSSAPI to get the Kerberos princ > found the ticket which is constructed? I'm pretty sure the MIT > libraries support that, at least... I expected I might be able to do that on Linux, but right now I'm trying to work out the Windows non-domain case. > Just as with .k5login, they do *not* have to match, but if they don't > then there needs to be a mapping provided from the Kerberos princ to the > PG username. Check out pg_ident and note that it even supports > regexp's, so you may be able to construct a mapping such that the princ > is mixed case and the login works- provided you send the lowercase'd > username as the PG user to log in as. Unfortunately, in this case I don't even have a wrong-cased username to start with. I have the user name of the logged-in non-domain user, which is not the user name of the domain credentials I'm sending across the network. >> I think Postgres should either not require or ignore the user name in the >> startup packet for these two login types. What do you think? > > We need the username to figure out which auth method we're using... Oh dear. --Brian -- 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] GSSAPI/SSPI and mismatched user names
* Brian Crowell (br...@fluggo.com) wrote: > https://github.com/npgsql/Npgsql/issues/162#issuecomment-35916650 Reading through this- can't you use GSSAPI to get the Kerberos princ found the ticket which is constructed? I'm pretty sure the MIT libraries support that, at least... > The short version is that Postgres requires two user names when using > GSSAPI/SSPI: one from the startup packet, and one from the Kerberos ticket, > and if these don't match exactly, the login fails. It's generally > impossible to determine the correct user name to send in the startup packet. Just as with .k5login, they do *not* have to match, but if they don't then there needs to be a mapping provided from the Kerberos princ to the PG username. Check out pg_ident and note that it even supports regexp's, so you may be able to construct a mapping such that the princ is mixed case and the login works- provided you send the lowercase'd username as the PG user to log in as. > I think Postgres should either not require or ignore the user name in the > startup packet for these two login types. What do you think? We need the username to figure out which auth method we're using... Thanks, Stephen signature.asc Description: Digital signature
[GENERAL] GSSAPI/SSPI and mismatched user names
I'm going to file this as a bug as well, but I guess I'm hoping to catch some developers here for discussion. I'm working with the Npgsql group on getting integrated security to "just work" in the same way SQL Server's does. I wrote a workaround for one issue, only to find out that I need more workarounds, and I finally realized that this a problem with the way Postgres handles GSSAPI/SSPI logins. You can read my full description here: https://github.com/npgsql/Npgsql/issues/162#issuecomment-35916650 The short version is that Postgres requires two user names when using GSSAPI/SSPI: one from the startup packet, and one from the Kerberos ticket, and if these don't match exactly, the login fails. It's generally impossible to determine the correct user name to send in the startup packet. I think Postgres should either not require or ignore the user name in the startup packet for these two login types. What do you think? --Brian
Re: [GENERAL] Why does PostgreSQL ftruncate before unlink?
On Mon, Feb 24, 2014 at 6:38 PM, Jon Nelson wrote: > Here is an example. > > % time seconds usecs/call callserrors syscall > -- --- --- - - > 99.953.2076814182 767 ftruncate > 0.050.001579 1 2428 2301 unlink Are this times for unlink after ftruncate? Because ( in linux which is the one I use in the desktops and I'm familiar with ) unlinks of big files are slow too, so to have a more meaningful comparison you would need to time ftruncate+unlink and plain unlink of same files, IIRC they take nearly equal time. Francisco Olarte. -- 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] Why does PostgreSQL ftruncate before unlink?
On Sun, Feb 23, 2014 at 10:07 PM, Tom Lane wrote: > Jon Nelson writes: >> On Sun, Feb 23, 2014 at 9:49 PM, Tom Lane wrote: >>> If memory serves, the inode should get removed during the next checkpoint. > >> I was moments away from commenting to say that I had traced the flow >> of the code to md.c and found the comments there quite illuminating. I >> wonder if there is a different way to solve the underlying issue >> without relying on ftruncate (which seems to be somewhat expensive). > > Hm. The code is designed the way it is on the assumption that ftruncate > doesn't do anything that unlink wouldn't have to do anyway. If it really > is significantly slower on popular filesystems, maybe we need to revisit > that. > Here is an example. % time seconds usecs/call callserrors syscall -- --- --- - - 99.953.2076814182 767 ftruncate 0.050.001579 1 2428 2301 unlink -- Jon -- 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] stand by is starting until I do some work in the primary
On Sun, Feb 23, 2014 at 8:25 AM, Jov wrote: > Sure.Before the restart,I have login in the slave many times. > some more info ,but I don't think related: > centOS 6 ,amd64,128GB memory,24 core, pgsql-9.3.3,master and slave in the > same host. > > this is my first instance of 9.3，I don't exprence this before on 9.0~9.2. > > > It will probably work again after a CHECKPOINT (manual or automatic), if there is *some* activity on the master. You may be experiencing what the patch ed46758381ff63a422fcb0b1f2763eb13273640f, which will be included in 9.4. But the workaround is to make sure something happens on the master and do a checkpoint. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
