Re: execute_values
My insert has a "returning clause," I don't think execute batch supports that.
--
Gerard Weatherby| Application Architect
NMRbox | Department of Molecular Biology and Biophysics | UConn Health
263 Farmington Avenue, Farmington, CT 06030-6406
Phone: 860 679 8484
uchc.edu
From: Adrian Klaver
Sent: Tuesday, July 9, 2019 4:25 PM
To: Weatherby,Gerard; pgsql-general@lists.postgresql.org
Subject: Re: execute_values
On 7/9/19 11:04 AM, Weatherby,Gerard wrote:
> Can I use pyscopy2.extras.execute_values with a prepared statement?
I don't think so as you cannot pass in VALUES():
cur.execute('prepare pt(integer, varchar, boolean) AS INSERT INTO
prepare_test values ($1, $2, $3)')
execute_values(cur, 'EXECUTE pt(%s)', [(2, 'test', 'f'),])
SyntaxError: wrong number of parameters for prepared statement "pt"
DETAIL: Expected 3 parameters but got 1.
execute_batch works though:
execute_batch(cur, 'EXECUTE pt(%s, %s, %s)', [(2, 'test', 'f'), (3,
'dog', 't')])
>
>
> --
> *Gerard Weatherby*| Application Architect
> NMRbox | Department of Molecular Biology and Biophysics | UConn Health
> 263 Farmington Avenue, Farmington, CT 06030-6406
> Phone: 860 679 8484
> uchc.edu
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Restoring a database restores to unexpected tablespace
Ian Barwick writes: > On 7/10/19 2:56 AM, Alex Williams wrote: >> 3. Restore the database with this command: >> zcat /var/backup/db/mydatabase.gz | sudo -H -u postgres psql >> --quiet -e -c 'SET default_tablespace = pg_default;' -f - mydatabase_test > >> /tmp/mydatabase_test.log > This should work. Yeah, on modern PG. But I think psql didn't support combinations of -c and -f switches until 9.6. 9.5 would simply have dropped that -c switch. regards, tom lane
Re: execute_values
On 7/10/19 5:09 AM, Weatherby,Gerard wrote:
My insert has a "returning clause," I don't think execute batch supports that.
Well it will execute, it just will not return the values to you:(
What is your query and what are you doing with it?
--
Gerard Weatherby| Application Architect
NMRbox | Department of Molecular Biology and Biophysics | UConn Health
263 Farmington Avenue, Farmington, CT 06030-6406
Phone: 860 679 8484
uchc.edu
From: Adrian Klaver
Sent: Tuesday, July 9, 2019 4:25 PM
To: Weatherby,Gerard; pgsql-general@lists.postgresql.org
Subject: Re: execute_values
On 7/9/19 11:04 AM, Weatherby,Gerard wrote:
Can I use pyscopy2.extras.execute_values with a prepared statement?
I don't think so as you cannot pass in VALUES():
cur.execute('prepare pt(integer, varchar, boolean) AS INSERT INTO
prepare_test values ($1, $2, $3)')
execute_values(cur, 'EXECUTE pt(%s)', [(2, 'test', 'f'),])
SyntaxError: wrong number of parameters for prepared statement "pt"
DETAIL: Expected 3 parameters but got 1.
execute_batch works though:
execute_batch(cur, 'EXECUTE pt(%s, %s, %s)', [(2, 'test', 'f'), (3,
'dog', 't')])
--
*Gerard Weatherby*| Application Architect
NMRbox | Department of Molecular Biology and Biophysics | UConn Health
263 Farmington Avenue, Farmington, CT 06030-6406
Phone: 860 679 8484
uchc.edu
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Adrian Klaver
adrian.kla...@aklaver.com
DRY up GUI wiki pages
I learned today there are "two" wiki pages for GUI clients: https://wiki.postgresql.org/wiki/PostgreSQL_Clients https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools I'd like to DRY them up so there aren't two lists which confuses newcomers. Any objections? If not I'll probably make one of those pages into GUI's and one into "non GUI's" or something like that. Cheers!
Managing permissions for multiple users to Create and Drop tables
We have one database with several schemas. We have several groups of developers that have the need to be able to collaborate including creating and dropping tables. I noticed it became difficult to manage because when one developer creates a table, he is now the owner. All the other developers need to be given permissions to select from (or drop) those tables he created. I was looking for a way to where all members of a group can select from everybody’s tables, drop them if needed, etc. Where they can all work collaboratively. I found this site that had a suggestion: https://blog.hagander.net/setting-owner-at-create-table-237/ You basically create a “common_role” as the table owner and grant all the users access to tables owned by common_role. However, for this to work automatically, you need to create an event trigger so that each time a developer creates a table, it alters the table owner after the table is created. I was wonder how other DBA’s handle this permission issue for collaborative environments? Any best practices or advise?
Re: Restoring a database restores to unexpected tablespace
Thanks Tom and Ian, Tom, I wasn't sure if that would work (-c), so I just tried assuming it would throw an error, but it didn't so I assumed it worked until I started checking the tables and noticed it was still being created on data2. I tried originally with just -f, but that didn't work so I added in -c and that didn't work either. Last night, I had an idea and it's working the way I expect it to now, this is what I did: 1. Dump Database, this time, no compression so I can search/grep it to see if it has any tablespace references, PLUS add in my own tablspace reference (See Step 2 for that): sudo -u postgres pg_dump --no-owner --no-tablespaces mydatabase > /var/lib/pgsql/dumps/mydatabase.dump 2. Set the default table to pg_default on the first line with sed: sed -i '1i SET default_tablespace = pg_default;' /var/lib/pgsql/dumps/mydatabase.dump 3. Restore the database: cat /var/lib/pgsql/dumps/mydatabase.dump | sudo -H -u postgres psql --quiet -e -c 'SET default_tablespace = pg_default;' -f - mydatabase_test > /tmp/mydatabase.log It's currently still restoring, will take about 2 more hours to complete, but so far so good. Also, Ian, good idea on that, but there was no issue of the db dump yesterday with path and possibly one path had a dump with tablespaces and the other didn't and that I possibly used the one with the tablespace in it...as I was in the directory for the dump and just explicitly wrote out the directory for the restore, and it's the first time I did this and the dump I took explicitly passed in no tablespaces. I couldn't read the file to be sure, so I added -e on the restore and checked the logs to see what it was doing and didn't see any mention of tablespaces. On the newer dump in plaintext, it also doesn't have any tablespace reference other than the one I added as the first line stating: SET default_tablespace = pg_default; Again, the restore to database was set with pg_default as the tablespace to use and the dump explicitly passed in the parameter for no tablespaces (and it looks like it didn't add in tablespace info) but on restore, it restored to tablespace data2 instead of the pg_default tablespace of the databaseonly after modifying the dump file by adding to the top line this: "SET default_tablespace = pg_default;" did it restore to the pg_default tablespace. Thanks again for your help! Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, July 10, 2019 10:20 AM, Tom Lane wrote: > Ian Barwick ian.barw...@2ndquadrant.com writes: > > > On 7/10/19 2:56 AM, Alex Williams wrote: > > > > > 3. Restore the database with this command: > > > zcat /var/backup/db/mydatabase.gz | sudo -H -u postgres > > > psql --quiet -e -c 'SET default_tablespace = pg_default;' -f - > > > mydatabase_test > /tmp/mydatabase_test.log > > > > > > This should work. > > Yeah, on modern PG. But I think psql didn't support combinations of > -c and -f switches until 9.6. 9.5 would simply have dropped that -c > switch. > > regards, tom lane
Re: DRY up GUI wiki pages
On Wed, Jul 10, 2019 at 09:08:30AM -0600, Roger Pack wrote: > I learned today there are "two" wiki pages for GUI clients: > > https://wiki.postgresql.org/wiki/PostgreSQL_Clients > > https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools > > I'd like to DRY them up so there aren't two lists which confuses > newcomers. Any objections? If not I'll probably make one of those > pages into GUI's and one into "non GUI's" or something like that. Agreed, a cleanup would be nice. :-) -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: pg_dump and search_path
I had a similar problem and was able to being the command with the search_path to work around it. I did this on Linux and it looks like you are on Windows but I maybe you can do something similar that will work? PGOPTIONS='-c search_path=staging, transient, pg_catalog' *Ryan Lambert* RustProof Labs >
Re: pg_dump and search_path
On 7/10/19 1:19 PM, Ryan Lambert wrote:
I had a similar problem and was able to being the command with the
search_path to work around it. I did this on Linux and it looks like
you are on Windows but I maybe you can do something similar that will work?
PGOPTIONS='-c search_path=staging, transient, pg_catalog'
Not sure how that worked:
export PGOPTIONS="-c search_path=public"
psql -d test -U postgres
psql (11.4)
Type "help" for help.
test_(postgres)# show search_path;
search_path
-
public
test_(postgres)# SELECT pg_catalog.set_config('search_path', '', false);
set_config
(1 row)
test_(postgres)# show search_path;
search_path
-
(1 row)
*Ryan Lambert*
RustProof Labs
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: DRY up GUI wiki pages
> On Jul 10, 2019, at 7:38 PM, Bruce Momjian wrote: > > On Wed, Jul 10, 2019 at 09:08:30AM -0600, Roger Pack wrote: >> I learned today there are "two" wiki pages for GUI clients: >> >> https://wiki.postgresql.org/wiki/PostgreSQL_Clients >> >> https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools >> >> I'd like to DRY them up so there aren't two lists which confuses >> newcomers. Any objections? If not I'll probably make one of those >> pages into GUI's and one into "non GUI's" or something like that. I created the first one because the second one was full of old, stale, useless things. I believe that everything valid on the second one was added to the first one at the time. Also look at https://wiki.postgresql.org/wiki/Design_Tools and https://wiki.postgresql.org/wiki/Documentation_Tools if you're looking to combine / divide things. Cheers, Steve > > Agreed, a cleanup would be nice. :-) > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > >
Re: pg_dump and search_path
My exact situation was a deployment via sqitch, It appears that uses psql under the hood based on the error message I get. Running just "sqitch deploy" I get an error due to a non-fully qualified name and a missing search path (my mistakes). The error I get: + 004 .. psql:deploy/004.sql:72: ERROR: relation "vobservations" does not exist LINE 11:FROM vobservations ^ not ok "psql" unexpectedly returned exit value 3 Reverting all changes Running the following works for me in this case and allows it to find the view in the proper schema. PGOPTIONS='-c search_path=piws,public' sqitch deploy Ryan
Re: pg_dump and search_path
On 7/10/19 4:31 PM, Ryan Lambert wrote: My exact situation was a deployment via sqitch, It appears that uses psql under the hood based on the error message I get. Yes it does: https://sqitch.org/docs/manual/sqitch/ "Native scripting Changes are implemented as scripts native to your selected database engine. Writing a PostgreSQL application? Write SQL scripts for psql. Writing an Oracle-backed app? Write SQL scripts for SQL*Plus." Running just "sqitch deploy" I get an error due to a non-fully qualified name and a missing search path (my mistakes). The error I get: + 004 .. psql:deploy/004.sql:72: ERROR: relation "vobservations" does not exist LINE 11: FROM vobservations ^ not ok "psql" unexpectedly returned exit value 3 Reverting all changes Running the following works for me in this case and allows it to find the view in the proper schema. PGOPTIONS='-c search_path=piws,public' sqitch deploy Would it not be easier to just set the search_path in postgresql.conf? Or if you want it just for Sqitch, modify the pg templates to include the search_path? Unfortunately in the OP's case the restore is going to overwrite the env setting. Ryan -- Adrian Klaver adrian.kla...@aklaver.com
