Prepared statements versus stored procedures
Hi, First of all please forgive me. I'm not very experienced with databases. I was reading about prepared statements and how they allow the server to plan the query in advance so that if you execute that query multiple times it gets sped up as the database has already done the planning work. My question is this. If I make a stored procedure doesn't the database already pre-plan and optimise the query because it has access to the whole query? Or could I create a stored procedure and then turn it into a prepared statement for more speed? I was also thinking a stored procedure would help as it requires less network round trips as the query is already on the server. Sorry for the question but I'm not entirely sure how stored procedures and prepared statements work together. signature.asc Description: OpenPGP digital signature
Re: Prepared statements versus stored procedures
On Sunday, 19 November 2023 at 18:09, Francisco Olarte wrote: > > > Hi Simon: > > On Sun, 19 Nov 2023 at 18:30, Simon Connah > simon.n.con...@protonmail.com wrote: > > > I was reading about prepared statements and how they allow the server to > > plan the query in advance so that if you execute that query multiple times > > it gets sped up as the database has already done the planning work. > > > But bear in mind that, if you use parameters, it does not have access > to the whole query, so it has to make a generic plan. Many times it > does not matter, but sometimes it does ( i.e. testing columns with > very skewed value distributions, if you have an X column, indexed, > where 99% of the values are 1 querying for X=1 is faster using a > sequential scan when X=1 and an index scan when not, if you send X in > a parameter the server does not know its real value ). > > > My question is this. If I make a stored procedure doesn't the database > > already pre-plan and optimise the query because it has access to the whole > > query? > > > IIRC it does not, because it may not have access to all values, and > more importantly, it does not have access to current statistics. Think > of the typical case, preparing a database for an application, with > empty tables and several procedures. On the first run, sequential > scans ( to recheck for emptiness ) will be faster for every query. > After some time of entering data ( and updating statistics ) better > plans will surface. If you compiled the procedures on definition you > would be stuck with seq scans forever. IIRC it does it once per > transaction, but it should be in the docs. > > > Or could I create a stored procedure and then turn it into a prepared > > statement for more speed? > > I was also thinking a stored procedure would help as it requires less > > network round trips as the query is already on the server. > > > The main speed improvement of stored procedures is normally the less > roundtrips ( and marshalling of queries back and forth ). You do not > turn a stored procedure into a statement, you turn CALLING the stored > procedure into a prepared statement, which may save some time but not > that much, planning a call is easy. > > Other thing would be turning a stored procedure call into a prepared > statement for an inline procedure, but this is something else. > > Francisco Olarte. Thank you very much for the explanation. I really appreciate it. Simon. signature.asc Description: OpenPGP digital signature
PostgreSQL DB in prod, test, debug
Hi, This project uses Python 3.12, Flask, psycopg3 and PostgreSQL 15. This is probably a stupid question so I apologies in advance. I'm building a website using PostgreSQL and since I've just been doing some dev work on it I've just manually played around with the database if I needed new tables or functions for example but I want to start doing automated testing and need to import a clean snapshot of the database with no data and then use the automated tests to test if things work with the tests. What I think is the best way to do this is to do a pg_dump of the database (using the --schema-only flag) and then load it into a test only database that gets created at the start of the unit tests and destroyed at the end. The automated tests will insert, update, delete and select data to test if it all still works. My main question is does this sound OK? And if so is there a nice way to automate the dump / restore in Python? Simon. signature.asc Description: OpenPGP digital signature
Re: PostgreSQL DB in prod, test, debug
On Wednesday, 14 February 2024 at 10:25, Daniel Gustafsson wrote: > > > > On 14 Feb 2024, at 10:59, Simon Connah simon.n.con...@protonmail.com wrote: > > > This is probably a stupid question so I apologies in advance. > > > There is no such thing. > > > What I think is the best way to do this is to do a pg_dump of the database > > (using the --schema-only flag) and then load it into a test only database > > that gets created at the start of the unit tests and destroyed at the end. > > The automated tests will insert, update, delete and select data to test if > > it all still works. > > > If the source of truth for your schema is the database, then sure. If the > source of truth is a .sql file in your source code repository then you should > use that. In essence, create the test database identically to how you create > the production database to ensure that you are testing what you will run in > production. > > -- > Daniel Gustafsson Thank you. I'm a bit disorganised with things at the moment. I'm kinda developing this site in an ad hoc manner which is probably a bad idea so I'm trying to fix up a few mistakes I made early on. In terms of the database I've just added new functions as needed which I use instead of direct SQL in my Python code. Maybe I should sit down with Visio and try and do a diagram before I go any further? I'll just write a simple script for backup and restore and call it before each test run but have the schema only dump come from production. At this point it doesn't really matter as the website is so small. Simon. signature.asc Description: OpenPGP digital signature
Re: PostgreSQL DB in prod, test, debug
On Wednesday, 14 February 2024 at 10:38, Erik Wienhold wrote: > > > On 2024-02-14 10:59 +0100, Simon Connah wrote: > > > This project uses Python 3.12, Flask, psycopg3 and PostgreSQL 15. > > > > This is probably a stupid question so I apologies in advance. > > > > I'm building a website using PostgreSQL and since I've just been doing > > some dev work on it I've just manually played around with the database > > if I needed new tables or functions for example but I want to start > > doing automated testing and need to import a clean snapshot of the > > database with no data and then use the automated tests to test if > > things work with the tests. > > > > What I think is the best way to do this is to do a pg_dump of the > > database (using the --schema-only flag) > > > You create a dump from the prod database each time? Yikes. Sorry. That came out wrong. I don't take a dump each time I run tests but I will generally take a full dump when working on something specific to the database. > > > and then load it into a test only database that gets created at the > > start of the unit tests and destroyed at the end. The automated tests > > will insert, update, delete and select data to test if it all still > > works. > > > > My main question is does this sound OK? And if so is there a nice way > > to automate the dump / restore in Python? > > > The database schema should be defined by migration scripts that you also > check into version control with the rest of your application sources. > Some people also prefer a separate repository just for the database > schema, depending on how tightly coupled database and application are, > or if there are other applications relying on the database schema. > > I use Sqitch[1] which works well if you want to track an existing > database schema. Alembic is popular in Python but I don't know how it > works with an existing schema because Alembic migrations are usually not > written in plain SQL. > > This is also useful for automated deployment because it allows you to > migrate the database to a specific schema version that is necessary for > your application. > > For testing, I have a Bash script that starts a Docker container with > Postgres and then runs sqitch-deploy before running pytest. That can of > course be adapted to spin up a local Postgres instance instead. I also > use pgTAP[2] with that to just test the database schema. > > You can also use testcontainers[3] to start/stop a Docker container in > pytest and run sqitch with subprocess before running all tests. > > [1] https://sqitch.org/ > [2] https://pgtap.org/ > [3] > https://testcontainers-python.readthedocs.io/en/latest/postgres/README.html > > -- > Erik Thank you. Squitch looks like a useful tool. I'll certainly look into all of that. I've never really used Docker before. Instead I just spin up Vultr instances but I can read about Docker as well. Simon. signature.asc Description: OpenPGP digital signature
I have no idea why pg_dump isn't dumping all of my data
Hi, I'm running the following command to dump my database: /usr/bin/pg_dump --file=/home/simon/nanoscopic_db_127_0_0_1-2021_05_21_10_25_28-dump.sql --dbname=nanoscopic_db --clean --create --if-exists --username=nanoscopic_db_user --host=127.0.0.1 --port=5432 and yet when I run that all I get in the SQL file is the following: https://gist.github.com/simonconnah/e1a15b1536b6e519b84481ae74f082bf I'm at a total loss. I've tried all the relevant looking command line switches and nothing seems to dump the actual contents of the database. It just dumps the extension command. Can anyone help me to figure this out please? It is probably something stupid that I am doing wrong. Simon. signature.asc Description: OpenPGP digital signature
Re: I have no idea why pg_dump isn't dumping all of my data
‐‐‐ Original Message ‐‐‐ On Friday, May 21st, 2021 at 10:55, Vijaykumar Jain wrote: > Can you try dumping using verbose flag. > -v > > Just want to confirm if the user has relevant permissions. > > On Fri, May 21, 2021, 3:04 PM Simon Connah > wrote: > > > Hi, > > > > I'm running the following command to dump my database: > > > > /usr/bin/pg_dump > > --file=/home/simon/nanoscopic_db_127_0_0_1-2021_05_21_10_25_28-dump.sql > > --dbname=nanoscopic_db --clean --create --if-exists > > --username=nanoscopic_db_user --host=127.0.0.1 --port=5432 > > > > and yet when I run that all I get in the SQL file is the following: > > > > https://gist.github.com/simonconnah/e1a15b1536b6e519b84481ae74f082bf > > > > I'm at a total loss. I've tried all the relevant looking command line > > switches and nothing seems to dump the actual contents of the database. It > > just dumps the extension command. Can anyone help me to figure this out > > please? It is probably something stupid that I am doing wrong. > > > > Simon. pg_dump: last built-in OID is 16383 pg_dump: reading extensions pg_dump: identifying extension members pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading user-defined functions pg_dump: reading user-defined types pg_dump: reading procedural languages pg_dump: reading user-defined aggregate functions pg_dump: reading user-defined operators pg_dump: reading user-defined access methods pg_dump: reading user-defined operator classes pg_dump: reading user-defined operator families pg_dump: reading user-defined text search parsers pg_dump: reading user-defined text search templates pg_dump: reading user-defined text search dictionaries pg_dump: reading user-defined text search configurations pg_dump: reading user-defined foreign-data wrappers pg_dump: reading user-defined foreign servers pg_dump: reading default privileges pg_dump: reading user-defined collations pg_dump: reading user-defined conversions pg_dump: reading type casts pg_dump: reading transforms pg_dump: reading table inheritance information pg_dump: reading event triggers pg_dump: finding extension tables pg_dump: finding inheritance relationships pg_dump: reading column info for interesting tables pg_dump: finding the columns and types of table "public.blog_user" pg_dump: finding default expressions of table "public.blog_user" pg_dump: finding check constraints for table "public.blog_user" pg_dump: finding the columns and types of table "public.blog" pg_dump: finding default expressions of table "public.blog" pg_dump: finding the columns and types of table "public.blog_post" pg_dump: finding default expressions of table "public.blog_post" pg_dump: finding check constraints for table "public.blog_post" pg_dump: finding the columns and types of table "public.blog_post_comment" pg_dump: finding default expressions of table "public.blog_post_comment" pg_dump: finding the columns and types of table "public.blog_page" pg_dump: finding default expressions of table "public.blog_page" pg_dump: finding the columns and types of table "public.blog_user_permissions" pg_dump: finding default expressions of table "public.blog_user_permissions" pg_dump: flagging inherited columns in subtables pg_dump: reading indexes pg_dump: reading indexes for table "public.blog_user" pg_dump: reading indexes for table "public.blog" pg_dump: reading indexes for table "public.blog_post" pg_dump: reading indexes for table "public.blog_post_comment" pg_dump: reading indexes for table "public.blog_page" pg_dump: reading indexes for table "public.blog_user_permissions" pg_dump: flagging indexes in partitioned tables pg_dump: reading extended statistics pg_dump: reading constraints pg_dump: reading triggers pg_dump: reading rewrite rules pg_dump: reading policies pg_dump: reading row security enabled for table "public.blog_user_blog_user_id_seq" pg_dump: reading policies for table "public.blog_user_blog_user_id_seq" pg_dump: reading row security enabled for table "public.blog_user" pg_dump: reading policies for table "public.blog_user" pg_dump: reading row security enabled for table "public.blog_blog_id_seq" pg_dump: reading policies for table "public.blog_blog_id_seq" pg_dump: reading row security enabled for table "public.blog" pg_dump: reading policies for table "public.blog" pg_dump: reading row security enabled for table "public.blog_post_blog_post_id_seq" pg_dump: reading policies for table "public.blog_pos
Re: I have no idea why pg_dump isn't dumping all of my data
This is the source code of the extension in question:
https://github.com/xmrsoftware/nanoscopic/tree/master/sql/nanoscopic
‐‐‐ Original Message ‐‐‐
On Friday, May 21st, 2021 at 11:29, Vijaykumar Jain
wrote:
> i just did a dump of a db which was owned by postgres but some tables owned
> by other users and it ran fine.I am not sure of that nanoscopic extension
> though.
>
> ***
> createdb -e foobar;
>
> postgres=# \c foobarYou are now connected to database "foobar" as user
> "postgres".foobar=# set role demo;SETfoobar=> create table xx(id int);CREATE
> TABLEfoobar=> \dt List of relations Schema | Name | Type |
> Owner+--+---+--- public | xx | table | demo(1 row)
>
> foobar=> insert into xx values (1);INSERT 0 1foobar=> \dt List of
> relations Schema | Name | Type | Owner+--+---+--- public
> | xx | table | demo(1 row)
>
> foobar=> \l List of databases Name |
> Owner | Encoding | Collate | Ctype | Access
> privileges---+--+--+++---
> demo | demo_rw | UTF8 | en_US.utf8 | en_US.utf8 | foobar |
> postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgres | postgres | UTF8
> | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 |
> en_US.utf8 | =c/postgres + | | |
> | | postgres=CTc/postgres template1 | postgres | UTF8 |
> en_US.utf8 | en_US.utf8 | =c/postgres + | |
> | | | postgres=CTc/postgres(5
> rows)***
>
> ***
> pg_dump --file=nanoscopic_db_127_0_0_1-2021_05_21_10_25_28-dump.sql
> --dbname=foobar --clean --create --if-exists --username=demo -v
> --host=127.0.0.1 --port=5432
>
> ... last lines from the verbose dump
>
> pg_dump: dropping DATABASE foobarpg_dump: creating DATABASE "foobar"pg_dump:
> connecting to new database "foobar"pg_dump: creating TABLE
> "public.xx"pg_dump: processing data for table "public.xx"pg_dump: dumping
> contents of table "public.xx"
>
> CREATE DATABASE foobar WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE =
> 'en_US.utf8';
>
> ALTER DATABASE foobar OWNER TO postgres;
>
> \connect foobar
>
> SET statement_timeout = 0;SET lock_timeout = 0;SET
> idle_in_transaction_session_timeout = 0;SET client_encoding = 'UTF8';SET
> standard_conforming_strings = on;SELECT pg_catalog.set_config('search_path',
> '', false);SET check_function_bodies = false;SET xmloption = content;SET
> client_min_messages = warning;SET row_security = off;
>
> SET default_tablespace = '';
>
> SET default_table_access_method = heap;
>
> TOC entry 200 (class 1259 OID 26105)-- Name: xx; Type: TABLE; Schema:
> public; Owner: demo--
>
> CREATE TABLE public.xx ( id integer);
>
> ALTER TABLE public.xx OWNER TO demo;
>
> TOC entry 2232 (class 0 OID 26105)-- Dependencies: 200-- Data for Name:
> xx; Type: TABLE DATA; Schema: public; Owner: demo--
>
> COPY public.xx (id) FROM stdin;1\.
>
> -- Completed on 2021-05-21 15:54:08 IST
>
> PostgreSQL database dump complete--***works
> fine.I do not know that extension(nanoscopic) though.
>
> it is reading some tables in a public schema, but not even dumping the schema.
>
> yep, thats odd if it does not throw any errors, coz any errors wrt
> permissions are thrown right away to console.
>
> maybe someone with more exp would be able to help.
>
> On Fri, 21 May 2021 at 15:32, Simon Connah
> wrote:
>
> > ‐‐‐ Original Message ‐‐‐
> > On Friday, May 21st, 2021 at 10:55, Vijaykumar Jain
> > wrote:
> >
> > > Can you try dumping using verbose flag.
> > > -v
> > >
> > > Just want to confirm if the user has relevant permissions.
> > >
> > > On Fri, May 21, 2021, 3:04 PM Simon Connah
> > > wrote:
> > >
> > > > Hi,
> > > >
> > > > I'm running the following command to dump my database:
> > > >
> > > > /usr/bin/pg_dump
> > > > --file=/home/simon/nanoscopic_db_127_0_0_1-2021_05_21_10_25_28-dump.sql
> > > > --dbname=nanoscopic_db --clean --create --if-exists
> > > > --username=nanoscopic_db_user --host=127.0.0.1 --port=5432
> > &g
Re: I have no idea why pg_dump isn't dumping all of my data
‐‐‐ Original Message ‐‐‐ On Friday, May 21st, 2021 at 12:46, Ian Lawrence Barwick wrote: > 2021年5月21日(金) 20:42 Vijaykumar Jain vijaykumarjain.git...@gmail.com: > > > PostgreSQL: Documentation: 13: 37.17. Packaging Related Objects into an > > Extension > > > > so it works as expected. > > > > someone would have to point to the reference wrt modification of data in > > objects created via extension. > > If you want to be able to dump data inserted into extension tables, > > the tables will need > > to be marked using "pg_extension_config_dump()" in the extension script, see: > > https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES > > and also: > > https://pgpedia.info/p/pg_extension_config_dump.html > > Regards > > Ian Barwick > > -- > > EnterpriseDB: https://www.enterprisedb.com I finally had a chance to test this out today and it seems to be fixed now. Thank you for the help. I would never have found that on my own. Simon. signature.asc Description: OpenPGP digital signature
