Re: [GENERAL] Alternative to psql -c ?
On Thu, 26 Jun 2014 15:00:56 -0700 Tom Lane wrote: > James Le Cuirot writes: > > Tom Lane wrote: > >> PG is not capable of executing queries that are not in > >> transactions, so yes, PQsendQuery will create a single-statement > >> transaction if you haven't sent BEGIN. However, there's a huge > >> difference for the purposes we're discussing here: PQsendQuery > >> does not allow more than one SQL command in the string. So most > >> of this discussion is irrelevant when you're going through that > >> API. > > > Heh, are you sure? From the docs... > > > "Using PQsendQuery and PQgetResult solves one of PQexec's problems: > > If a command string contains multiple SQL commands, the results of > > those commands can be obtained individually." > > Oh, sorry, I was confusing that with the extended-query-mode API > (PQexecParams). > > Yeah, PQsendQuery is like PQexec for this purpose --- the backend does > not actually know the difference. Okay so you're implying that it does the same kind of transaction wrapping as PQexec, right? But is it considered to be problematic and deprecated like PQexec or is it safe for multiple statements? Sorry for the never ending questions. :) James -- 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] Alternative to psql -c ?
James Le Cuirot writes: > Tom Lane wrote: >> PG is not capable of executing queries that are not in transactions, >> so yes, PQsendQuery will create a single-statement transaction if you >> haven't sent BEGIN. However, there's a huge difference for the >> purposes we're discussing here: PQsendQuery does not allow more than >> one SQL command in the string. So most of this discussion is >> irrelevant when you're going through that API. > Heh, are you sure? From the docs... > "Using PQsendQuery and PQgetResult solves one of PQexec's problems: If a > command string contains multiple SQL commands, the results of those > commands can be obtained individually." Oh, sorry, I was confusing that with the extended-query-mode API (PQexecParams). Yeah, PQsendQuery is like PQexec for this purpose --- the backend does not actually know the difference. 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] Alternative to psql -c ?
On Thu, 26 Jun 2014 11:02:09 -0700
Tom Lane wrote:
> James Le Cuirot writes:
> > This got me wondering what Rails uses. I dug into ActiveRecord and
> > found that apart from the odd call to PQexec with hardcoded single
> > statements, it uses PQsendQuery. The libpq docs state a few of the
> > differences but don't mention whether PQsendQuery automatically
> > creates a transaction like PQexec does. Please could you clarify
> > this?
>
> PG is not capable of executing queries that are not in transactions,
> so yes, PQsendQuery will create a single-statement transaction if you
> haven't sent BEGIN. However, there's a huge difference for the
> purposes we're discussing here: PQsendQuery does not allow more than
> one SQL command in the string. So most of this discussion is
> irrelevant when you're going through that API.
Heh, are you sure? From the docs...
"Using PQsendQuery and PQgetResult solves one of PQexec's problems: If a
command string contains multiple SQL commands, the results of those
commands can be obtained individually."
I also seem to be able to execute multiple statements at a time through
ActiveRecord. This method is just a thin wrapper around PQsendQuery.
Granted I only get the last result but if I change the first statement
to something erroneous, it does fail.
1.9.1 :001 > ActiveRecord::Base.connection.execute("SELECT 1; SELECT 2").first
=> {"?column?"=>"2"}
Regards,
James
--
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] Alternative to psql -c ?
James Le Cuirot writes: > This got me wondering what Rails uses. I dug into ActiveRecord and > found that apart from the odd call to PQexec with hardcoded single > statements, it uses PQsendQuery. The libpq docs state a few of the > differences but don't mention whether PQsendQuery automatically creates > a transaction like PQexec does. Please could you clarify this? PG is not capable of executing queries that are not in transactions, so yes, PQsendQuery will create a single-statement transaction if you haven't sent BEGIN. However, there's a huge difference for the purposes we're discussing here: PQsendQuery does not allow more than one SQL command in the string. So most of this discussion is irrelevant when you're going through that API. 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] Alternative to psql -c ?
On Thu, Jun 26, 2014 at 7:59 AM, James Le Cuirot wrote: > On Thu, 26 Jun 2014 07:23:02 -0500 > Merlin Moncure wrote: >> To be clear, Tom was advising not to rely on some of the quirky >> aspects of -c. psql as it stands right now has a some limitations: >> single transaction mode does not work with stdin and there is no >> reasonable way to pass multiple scripts through the command line. >> Adding it up this means that for generic multiple .sql passing you >> have to wrap psql with a script. > > I never said that I wanted to pass multiple scripts, just singular > scripts who's contents might be too large for the command line limit, > and that they might contain transaction statements. yup. Passing huge scripts via -c is definitely not a good practice. -c is designed for things like firing off a stored procedure from cron or bash variable assignment (for example: FOO=`psql -tXAqc"select bar()"`) > These scripts come from a Ruby string buffer so passing them via stdin > would be preferable to having to dump them out to a file first. You say > that single transaction mode doesn't work with stdin but it looks like > this was fixed in 9.3. yup -- i keep forgetting that. too many years of not having it i suppose. 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] Alternative to psql -c ?
On Thu, 26 Jun 2014 07:23:02 -0500 Merlin Moncure wrote: > On Thu, Jun 26, 2014 at 4:30 AM, James Le Cuirot > wrote: > > On Wed, 25 Jun 2014 13:21:44 -0500 > > Merlin Moncure wrote: > > > >> > The cookbook currently uses PQexec so multiple SQL commands are > >> > wrapped in a transaction unless an explicit transaction > >> > instruction appears. I don't want to change this behaviour but > >> > the only way to get exactly the same effect from psql is to use > >> > the -c option. > >> > > >> > I suspect some may shove rather large SQL scripts through this to > >> > the extent that it may break the command line limit, if not on > >> > Linux, then perhaps on Windows, where I gather it's 32,768. > >> > Passing these scripts on the command line doesn't seem > >> > particularly elegant in any case. I'd really like to use stdin > >> > but this has different transactional behaviour. I thought about > >> > looking for instances of transaction instructions in advance but > >> > I have seen that PostgreSQL does not do this naively; it uses > >> > the lexer. > >> > > >> > Is there another way? > >> > >> If I understand you properly (not sure), I pretty commonly get > >> around this via 'cat'. > >> > >> cat \ > >> <(echo "BEGIN;") \ > >> <(echo "\set ON_ERROR_STOP") \ > >> foo.sql bar.sql etc > >> <(echo "COMMIT;") \ > >> | psql ... > > > > This would work but given that this will be part of a public and > > widely-used cookbook, it needs to be able to deal with any scripts > > that will be thrown at it. Some of these may contain transactional > > statements and these will not work properly if wrapped in a big > > BEGIN/COMMIT. Having said that, Tom Lane has suggested that we > > should not rely on the existing transactional behaviour so maybe > > we'll need to be more explicit about whether we actually want a > > transaction or not. > > To be clear, Tom was advising not to rely on some of the quirky > aspects of -c. psql as it stands right now has a some limitations: > single transaction mode does not work with stdin and there is no > reasonable way to pass multiple scripts through the command line. > Adding it up this means that for generic multiple .sql passing you > have to wrap psql with a script. I never said that I wanted to pass multiple scripts, just singular scripts who's contents might be too large for the command line limit, and that they might contain transaction statements. I was looking for some way to replicate the -c behaviour without actually passing the contents on the command line but Tom says I would not want that behaviour anyway. So in that case, assuming I allow the existing behaviour to be modified and make single transaction mode an explicit option for the Chef users... These scripts come from a Ruby string buffer so passing them via stdin would be preferable to having to dump them out to a file first. You say that single transaction mode doesn't work with stdin but it looks like this was fixed in 9.3. I'd like this to work with earlier versions though so maybe "psql -1 -f -" would be the way to go. The man page says that this is subtly different from using < but doesn't mention transactions specifically. I hope this works. Regards, James -- 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] Alternative to psql -c ?
On Thu, Jun 26, 2014 at 4:30 AM, James Le Cuirot wrote: > On Wed, 25 Jun 2014 13:21:44 -0500 > Merlin Moncure wrote: > >> > The cookbook currently uses PQexec so multiple SQL commands are >> > wrapped in a transaction unless an explicit transaction >> > instruction appears. I don't want to change this behaviour but >> > the only way to get exactly the same effect from psql is to use >> > the -c option. >> > >> > I suspect some may shove rather large SQL scripts through this to >> > the extent that it may break the command line limit, if not on >> > Linux, then perhaps on Windows, where I gather it's 32,768. Passing >> > these scripts on the command line doesn't seem particularly elegant >> > in any case. I'd really like to use stdin but this has different >> > transactional behaviour. I thought about looking for instances of >> > transaction instructions in advance but I have seen that PostgreSQL >> > does not do this naively; it uses the lexer. >> > >> > Is there another way? >> >> If I understand you properly (not sure), I pretty commonly get around >> this via 'cat'. >> >> cat \ >> <(echo "BEGIN;") \ >> <(echo "\set ON_ERROR_STOP") \ >> foo.sql bar.sql etc >> <(echo "COMMIT;") \ >> | psql ... > > This would work but given that this will be part of a public and > widely-used cookbook, it needs to be able to deal with any scripts that > will be thrown at it. Some of these may contain transactional > statements and these will not work properly if wrapped in a big > BEGIN/COMMIT. Having said that, Tom Lane has suggested that we should > not rely on the existing transactional behaviour so maybe we'll need to > be more explicit about whether we actually want a transaction or not. To be clear, Tom was advising not to rely on some of the quirky aspects of -c. psql as it stands right now has a some limitations: single transaction mode does not work with stdin and there is no reasonable way to pass multiple scripts through the command line. Adding it up this means that for generic multiple .sql passing you have to wrap psql with a script. It'd be neat if psql had some xargs compatible facility for passing multiple files. This is complicated by the assumption that the unadorned argument is the database. Suppose though if the -f switch is arguments past the first are assumed to be files. Then you could do: psql foodb -1f foo.sql bar.sql baz.sql or find . | xargs psql foodb -1f (it's arguably cleaner to allow multiple -f arguments, but that's a lot more problematic for xargs type usage). As things stand today though, AFAICT the best way to consolidate scripts is to build a big script out of small ones. I realize that stinks from your point of view since not everyone will want to use unix/bash... 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] Alternative to psql -c ?
On Wed, 25 Jun 2014 17:30:15 +0200 hubert depesz lubaczewski wrote: > On Wed, Jun 25, 2014 at 5:18 PM, James Le Cuirot > wrote: > > > > Also - I have no idea what "peer authentication" has to do with Pg > > > gem - care to elaborate? The gem is for client, and authentication > > > happens in server, so ... ? > > Right but peer authentication is all to do with the operating system > > user that the client is connecting from. In the case of chef-client, > > > > Any reason why you can't reconfigure Pg to allow root connections to > postgres account? I had considered this. In practise, the "end user" would also need to be added. The postgresql cookbook currently doesn't generate the pg_ident.conf file but it could. However, this would still require the installation of build-essential and Tom Lane has pointed out that using PQexec is flawed anyway so maybe I could use stdin after all. James -- 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] Alternative to psql -c ?
On Wed, 25 Jun 2014 10:34:57 -0500 Jerry Sievers wrote: > > The cookbook currently uses PQexec so multiple SQL commands are > > wrapped in a transaction unless an explicit transaction > > instruction appears. I don't want to change this behaviour but > > the only way to get exactly the same effect from psql is to use > > the -c option. > > > > I suspect some may shove rather large SQL scripts through this to > > the extent that it may break the command line limit, if not on > > Linux, then perhaps on Windows, where I gather it's 32,768. Passing > > these scripts on the command line doesn't seem particularly elegant > > in any case. I'd really like to use stdin but this has different > > transactional behaviour. I thought about looking for instances of > > transaction instructions in advance but I have seen that PostgreSQL > > does not do this naively; it uses the lexer. > > > > Is there another way? > > Forget about trying to use psql -c since you try doing anything > non-trivial via this method and quoting will be at least one of your > headaches. Ruby executes psql using a kernel exec call and each argument is passed distinctly without any quoting required so that doesn't seem to be a problem at least. > Write a simpel $your-fav-scripting-lang client that passes stdin into > a single executor call... > > #!/usr/bin/python > > import psycopg2, sys > > conn = psycopg2.connect(...) > cur = conn.cursor( > > cur.execute(sys.stdin.read()) > conn.commit() This would work but probably wouldn't fly with the Chef guys as they'd want to know why it's installing modules for $my-fav-scripting-lang, which may != $their-fav-scripting-lang. Chances are that this would be Ruby in both cases but not every system packages the pg gem and that leads to the build-essential headache. > PS: Complex multi-statement executor calls are somewhat nuanced in > their own ways and I would be trying hard *not* to do this without > very good reason. Tom Lane has since said as much so I agree that a different approach is needed. I'll see if I can convince them. James -- 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] Alternative to psql -c ?
On Wed, 25 Jun 2014 13:21:44 -0500 Merlin Moncure wrote: > > The cookbook currently uses PQexec so multiple SQL commands are > > wrapped in a transaction unless an explicit transaction > > instruction appears. I don't want to change this behaviour but > > the only way to get exactly the same effect from psql is to use > > the -c option. > > > > I suspect some may shove rather large SQL scripts through this to > > the extent that it may break the command line limit, if not on > > Linux, then perhaps on Windows, where I gather it's 32,768. Passing > > these scripts on the command line doesn't seem particularly elegant > > in any case. I'd really like to use stdin but this has different > > transactional behaviour. I thought about looking for instances of > > transaction instructions in advance but I have seen that PostgreSQL > > does not do this naively; it uses the lexer. > > > > Is there another way? > > If I understand you properly (not sure), I pretty commonly get around > this via 'cat'. > > cat \ > <(echo "BEGIN;") \ > <(echo "\set ON_ERROR_STOP") \ > foo.sql bar.sql etc > <(echo "COMMIT;") \ > | psql ... This would work but given that this will be part of a public and widely-used cookbook, it needs to be able to deal with any scripts that will be thrown at it. Some of these may contain transactional statements and these will not work properly if wrapped in a big BEGIN/COMMIT. Having said that, Tom Lane has suggested that we should not rely on the existing transactional behaviour so maybe we'll need to be more explicit about whether we actually want a transaction or not. Thanks anyway, James -- 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] Alternative to psql -c ?
On Wed, 25 Jun 2014 09:04:44 -0700 Tom Lane wrote: > James Le Cuirot writes: > > hubert depesz lubaczewski wrote: > >> Perhaps you can explain what is the functionality you want to > >> achieve, as I, for one, don't understand. Do you want transactions? > >> Or not? > > > I want an implicit transaction around the whole script if no > > explicit transactions are present in the script. This is what > > psql's -c option does and this is also what the pg gem does because > > both use PQexec. > In short, I think it would be good to push back on the way Chef is > doing things now, not perpetuate a dependency on a legacy behavior. The mention of "legacy behaviour" and "unexpected results" in the psql man page hadn't gone unnoticed but I didn't think I would be able to convince the Chef guys to change their approach based on that. I think I stand a much better chance now that you of all people have said it though so thanks. :) This got me wondering what Rails uses. I dug into ActiveRecord and found that apart from the odd call to PQexec with hardcoded single statements, it uses PQsendQuery. The libpq docs state a few of the differences but don't mention whether PQsendQuery automatically creates a transaction like PQexec does. Please could you clarify this? Regards, James -- 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] Alternative to psql -c ?
On Wed, Jun 25, 2014 at 8:43 AM, James Le Cuirot wrote: > Hello, > > I've been using the Chef database cookbook and found it > frustrating because it doesn't allow you to use peer > authentication. The client process generally runs as root and > connects to PostgreSQL using the Ruby pg gem. > > I have patched it to shell out to psql instead. This has the > added benefit of not having to install "build-essential" on boxes > that might otherwise be very minimal. This is normally needed to > build the pg gem. I would therefore like to push the shell out > approach but one problem remains. > > The cookbook currently uses PQexec so multiple SQL commands are > wrapped in a transaction unless an explicit transaction > instruction appears. I don't want to change this behaviour but > the only way to get exactly the same effect from psql is to use > the -c option. > > I suspect some may shove rather large SQL scripts through this to the > extent that it may break the command line limit, if not on Linux, then > perhaps on Windows, where I gather it's 32,768. Passing these scripts > on the command line doesn't seem particularly elegant in any case. I'd > really like to use stdin but this has different transactional > behaviour. I thought about looking for instances of transaction > instructions in advance but I have seen that PostgreSQL does not do > this naively; it uses the lexer. > > Is there another way? If I understand you properly (not sure), I pretty commonly get around this via 'cat'. cat \ <(echo "BEGIN;") \ <(echo "\set ON_ERROR_STOP") \ foo.sql bar.sql etc <(echo "COMMIT;") \ | psql ... 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] Alternative to psql -c ?
James Le Cuirot writes: > hubert depesz lubaczewski wrote: >> Perhaps you can explain what is the functionality you want to >> achieve, as I, for one, don't understand. Do you want transactions? >> Or not? > I want an implicit transaction around the whole script if no explicit > transactions are present in the script. This is what psql's -c option > does and this is also what the pg gem does because both use PQexec. Well, psql -c (and also multiple commands in PQexec) have more odd behaviors than that. Notably, any error causes abandonment of the *whole* string, even if it's got begin/end commands in it. Most people think that psql -c's behavior is a legacy thing that we ought to change someday; if backwards compatibility weren't such a big deal around here, we might've done it already. You can find multiple bugs in the pgsql-bugs archives that amount to complaints that psql -c ought to work more like reading from stdin or a file. I don't think it's a good idea for you to try to arrange things so that untold amounts of code is depending on that behavior never changing. In short, I think it would be good to push back on the way Chef is doing things now, not perpetuate a dependency on a legacy behavior. 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] Alternative to psql -c ?
James Le Cuirot writes: > Hello, > > I've been using the Chef database cookbook and found it > frustrating because it doesn't allow you to use peer > authentication. The client process generally runs as root and > connects to PostgreSQL using the Ruby pg gem. > > I have patched it to shell out to psql instead. This has the > added benefit of not having to install "build-essential" on boxes > that might otherwise be very minimal. This is normally needed to > build the pg gem. I would therefore like to push the shell out > approach but one problem remains. > > The cookbook currently uses PQexec so multiple SQL commands are > wrapped in a transaction unless an explicit transaction > instruction appears. I don't want to change this behaviour but > the only way to get exactly the same effect from psql is to use > the -c option. > > I suspect some may shove rather large SQL scripts through this to the > extent that it may break the command line limit, if not on Linux, then > perhaps on Windows, where I gather it's 32,768. Passing these scripts > on the command line doesn't seem particularly elegant in any case. I'd > really like to use stdin but this has different transactional > behaviour. I thought about looking for instances of transaction > instructions in advance but I have seen that PostgreSQL does not do > this naively; it uses the lexer. > > Is there another way? Forget about trying to use psql -c since you try doing anything non-trivial via this method and quoting will be at least one of your headaches. Write a simpel $your-fav-scripting-lang client that passes stdin into a single executor call... #!/usr/bin/python import psycopg2, sys conn = psycopg2.connect(...) cur = conn.cursor( cur.execute(sys.stdin.read()) conn.commit() HTH) PS: Complex multi-statement executor calls are somewhat nuanced in their own ways and I would be trying hard *not* to do this without very good reason. > Regards, > James -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Alternative to psql -c ?
Re: James Le Cuirot 2014-06-25 <20140625144325.49d1124d@red.yakaraplc.local> > Hello, > > I've been using the Chef database cookbook and found it > frustrating because it doesn't allow you to use peer > authentication. The client process generally runs as root and > connects to PostgreSQL using the Ruby pg gem. > > I have patched it to shell out to psql instead. This has the No idea on the "psql -c" part, but I'd try using the pg gem, and use a .pgpass file along with md5 authentication. If you set the PGHOST env var (or PGUSER, PGSERVICE, ...), you don't even need to change any code for that to work with the usual PG default configs. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- 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] Alternative to psql -c ?
On Wed, Jun 25, 2014 at 5:18 PM, James Le Cuirot wrote: > > Also - I have no idea what "peer authentication" has to do with Pg > > gem - care to elaborate? The gem is for client, and authentication > > happens in server, so ... ? > Right but peer authentication is all to do with the operating system > user that the client is connecting from. In the case of chef-client, > Any reason why you can't reconfigure Pg to allow root connections to postgres account? depesz
Re: [GENERAL] Alternative to psql -c ?
On Wed, 25 Jun 2014 16:42:53 +0200 hubert depesz lubaczewski wrote: > On Wed, Jun 25, 2014 at 4:37 PM, James Le Cuirot > wrote: > > > On Wed, 25 Jun 2014 10:24:53 -0400 > > Andrew Sullivan wrote: > > > > > On Wed, Jun 25, 2014 at 03:16:19PM +0100, James Le Cuirot wrote: > > > > Same problem as stdin, the transactional behaviour is different. > > > > There is the --single-transaction option but as the man page > > > > says... > > > > > > > > "If the script itself uses BEGIN, COMMIT, or ROLLBACK, this > > > > option will not have the desired effects." > > > > > > Hmm. I've _used_ transactions in such files, I'm pretty sure. > > > You don't need the --single-transaction setting for this, just do > > > the BEGIN; and COMMIT; yourself. > > > > > > A > > > > Sorry, you're missing the point. I'm trying not to alter the > > existing behaviour of the Chef database cookbook which is used by > > countless people to execute scripts big and small, with and without > > transactions. If I just naively wrapped them all in BEGIN/COMMIT > > then it would override any additional transactions within the > > scripts. > > > > James > > Perhaps you can explain what is the functionality you want to > achieve, as I, for one, don't understand. Do you want transactions? > Or not? I want an implicit transaction around the whole script if no explicit transactions are present in the script. This is what psql's -c option does and this is also what the pg gem does because both use PQexec. This behaviour is described near the top of these pages: http://www.postgresql.org/docs/devel/static/libpq-exec.html http://www.postgresql.org/docs/devel/static/app-psql.html > Also - I have no idea what "peer authentication" has to do with Pg > gem - care to elaborate? The gem is for client, and authentication > happens in server, so ... ? Right but peer authentication is all to do with the operating system user that the client is connecting from. In the case of chef-client, this is almost always root. Chef cookbooks typically want to create databases and populate them with tables. Creating databases is usually done by the postgres user. Since the database cookbook currently doesn't shell out, the postgres user must be given a password. Similarly, creating tables is usually done by the user that will eventually use them, and this user must also be given a password. Needlessly creating passwords seems like a bad idea, especially since these passwords get stored in the central Chef database. Now I did consider that root could shell out to Ruby and use the pg gem as another user but that would lose the benefit of not having to install build-essential. Hope this makes sense now. Regards, James -- 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] Alternative to psql -c ?
Perhaps you can explain what is the functionality you want to achieve, as I, for one, don't understand. Do you want transactions? Or not? Also - I have no idea what "peer authentication" has to do with Pg gem - care to elaborate? The gem is for client, and authentication happens in server, so ... ? depesz On Wed, Jun 25, 2014 at 4:37 PM, James Le Cuirot wrote: > On Wed, 25 Jun 2014 10:24:53 -0400 > Andrew Sullivan wrote: > > > On Wed, Jun 25, 2014 at 03:16:19PM +0100, James Le Cuirot wrote: > > > Same problem as stdin, the transactional behaviour is different. > > > There is the --single-transaction option but as the man page says... > > > > > > "If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option > > > will not have the desired effects." > > > > Hmm. I've _used_ transactions in such files, I'm pretty sure. You > > don't need the --single-transaction setting for this, just do the > > BEGIN; and COMMIT; yourself. > > > > A > > Sorry, you're missing the point. I'm trying not to alter the existing > behaviour of the Chef database cookbook which is used by countless > people to execute scripts big and small, with and without transactions. > If I just naively wrapped them all in BEGIN/COMMIT then it would > override any additional transactions within the scripts. > > James > > > -- > 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] Alternative to psql -c ?
On Wed, Jun 25, 2014 at 03:37:11PM +0100, James Le Cuirot wrote: > Sorry, you're missing the point. I'm trying not to alter the existing > behaviour of the Chef database cookbook Ah, got it. Sorry, I'm clueless. No, I don't think I have a suggestion, then. A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Alternative to psql -c ?
On Wed, 25 Jun 2014 10:24:53 -0400 Andrew Sullivan wrote: > On Wed, Jun 25, 2014 at 03:16:19PM +0100, James Le Cuirot wrote: > > Same problem as stdin, the transactional behaviour is different. > > There is the --single-transaction option but as the man page says... > > > > "If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option > > will not have the desired effects." > > Hmm. I've _used_ transactions in such files, I'm pretty sure. You > don't need the --single-transaction setting for this, just do the > BEGIN; and COMMIT; yourself. > > A Sorry, you're missing the point. I'm trying not to alter the existing behaviour of the Chef database cookbook which is used by countless people to execute scripts big and small, with and without transactions. If I just naively wrapped them all in BEGIN/COMMIT then it would override any additional transactions within the scripts. James -- 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] Alternative to psql -c ?
On Wed, Jun 25, 2014 at 03:16:19PM +0100, James Le Cuirot wrote: > Same problem as stdin, the transactional behaviour is different. There > is the --single-transaction option but as the man page says... > > "If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option will > not have the desired effects." Hmm. I've _used_ transactions in such files, I'm pretty sure. You don't need the --single-transaction setting for this, just do the BEGIN; and COMMIT; yourself. A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Alternative to psql -c ?
On Wed, 25 Jun 2014 10:09:18 -0400 Andrew Sullivan wrote: > On Wed, Jun 25, 2014 at 02:43:25PM +0100, James Le Cuirot wrote: > > > The cookbook currently uses PQexec so multiple SQL commands are > > wrapped in a transaction unless an explicit transaction > > instruction appears. I don't want to change this behaviour but > > the only way to get exactly the same effect from psql is to use > > the -c option. > > Why not just read a file? -f filename. > > A Same problem as stdin, the transactional behaviour is different. There is the --single-transaction option but as the man page says... "If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option will not have the desired effects." Regards, James -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
