help implementing OGR Postgres foreign data wrapper

Hi, Apologies, this not strictly a Postgres question, but Postgres is so fundamental to what I'm trying to do and this list is generally so helpful, so here I go... I'm using FDW's to create virtual tables providing access to data from external data sources. This is working well for data in ot

Re: Growth planning

As for clustering, unfortunately, it's a one-time operation in Postgres (as far as I'm aware), so you'd have to "cluster" the index every time after an insert or update of data. If it is partitioned, I presume it can be run on the index of each partition table individually - but I'm not sure. On M

Re: How to build psycopg2 for Windows

Daniele (namesake), Thanks for the quick response. I tried that, it looks like https://github.com/psycopg/psycopg2/blob/master/scripts/build/appveyor.py#L291 shows the build step: python setup.py build_ext -l "libpgcommon libpgport" After that, I still get a binary that depends on libpq.dll - b

Re: How to build psycopg2 for Windows

On Tue, 5 Oct 2021 at 00:30, Dan Davis wrote: > > Can anyone give me a solution to build psycopg2 statically on Windows? You can follow what Appveyor does, which is the CI that builds psycopg2 packages. - this is the setup https://github.com/psycopg/psycopg2/blob/master/.appveyor/packages.yml -

How to build psycopg2 for Windows

Can anyone give me a solution to build psycopg2 statically on Windows? I have succeeded in building it, but when I run dumpbin /dependents on the generated file (the PYD file), it still depends on libpq.dll even when I pass --static-libpq. *Environment* - OS: Windows 10 - Psycopg version: psycop

Re: Growth planning

On 10/4/21 3:37 PM, Israel Brewster wrote: On Oct 4, 2021, at 1:21 PM, Rob Sargent > wrote: My "strict" table per station suggestion was meant as an option to avoid the partitioning pain point entirely if it wasn't going to buy you anything. Namely querying more t

Re: Growth planning

> On Oct 4, 2021, at 9:22 AM, Rob Sargent wrote: > > I think the date-station-channel could "take over" for the station-date. > Naturally the latter is chosen if you give just the two fields, but I would > be curious to see how well the former performs given just its first two > fields(when s

Re: Growth planning

> On Oct 4, 2021, at 1:21 PM, Rob Sargent wrote: > > On 10/4/21 3:09 PM, Israel Brewster wrote: >>> On Oct 4, 2021, at 12:46 PM, Ron >> > wrote: >>> >>> On 10/4/21 12:36 PM, Israel Brewster wrote: >>> [snip] Indeed. Table per station as opposed to partitionin

Re: Growth planning

On 10/4/21 3:09 PM, Israel Brewster wrote: On Oct 4, 2021, at 12:46 PM, Ron > wrote: On 10/4/21 12:36 PM, Israel Brewster wrote: [snip] Indeed. Table per station as opposed to partitioning? The *most* I can reasonably envision needing is to query two stations, i.

Query time related to limit clause

Hi Team, *I have shared execution times of two queries below:* *I need to find only the first row matching the criteria , but limit 1 is taking more time than limit 15 or more.. If any one can tell an explanation for this and how I can achieve the same in less time.* explain analyze SELECT * fr

Re: Growth planning

> On Oct 4, 2021, at 12:46 PM, Ron wrote: > > On 10/4/21 12:36 PM, Israel Brewster wrote: > [snip] >> Indeed. Table per station as opposed to partitioning? The *most* I can >> reasonably envision needing is to query two stations, i.e. I could see >> potentially wanting to compare station a to s

Re: Growth planning

On 10/4/21 12:36 PM, Israel Brewster wrote: [snip] Indeed. Table per station as opposed to partitioning? The *most* I can reasonably envision needing is to query two stations, i.e. I could see potentially wanting to compare station a to some “baseline” station b. In general, though, the station

Re: Growth planning

> On 4 Oct 2021, at 18:22, Israel Brewster wrote: (…) > the script owner is taking about wanting to process and pull in “all the > historical data we have access to”, which would go back several years, not to > mention the probable desire to keep things running into the foreseeable > future

Re: DELETE ... USING LATERAL

Michael Lewis writes: > On Mon, Oct 4, 2021, 10:30 AM Nikhil Benesch > wrote: >> # delete from int_arrays using int_arrays; >> ERROR: table name "int_arrays" specified more than once >> Don't you need to use an alias for the table in the using clause? You could, but then you'd be creating a se

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

On 2021-Jun-22, Mike Yeap wrote: > I have a Postgres version 11.11 configured with both physical replication > slots (for repmgr) as well as some logical replication slots (for AWS > Database Migration Service (DMS)). This morning, the server went panic with > the following messages found in the l

Re: DELETE ... USING LATERAL

Nikhil Benesch writes: > On Mon, Oct 4, 2021 at 1:48 PM Tom Lane wrote: >> My mental model of these things is that the target table is cross-joined >> to the additional tables as though by a comma in FROM [...] > Mine as well. > I just managed to dredge up some history here though. Turns out yo

Re: DELETE ... USING LATERAL

On Mon, Oct 4, 2021, 10:30 AM Nikhil Benesch wrote: > you can't reuse the FROM table name in the USING clause: > > # delete from int_arrays using int_arrays; > ERROR: table name "int_arrays" specified more than once > Don't you need to use an alias for the table in the using clause?

Re: DELETE ... USING LATERAL

On Mon, Oct 4, 2021 at 1:48 PM Tom Lane wrote: > My mental model of these things is that the target table is cross-joined > to the additional tables as though by a comma in FROM [...] Mine as well. I just managed to dredge up some history here though. Turns out you explicitly disabled this featu

Re: DELETE ... USING LATERAL

Nikhil Benesch writes: > Is it intentional that LATERAL elements in a USING clause of a DELETE > statement can't reference the table declared in the FROM clause? Hmm ... doesn't work for UPDATE, either. My mental model of these things is that the target table is cross-joined to the additional ta

Re: Growth planning

> On Oct 4, 2021, at 9:22 AM, Rob Sargent wrote: > > On 10/4/21 11:09 AM, Israel Brewster wrote: >>> On Oct 4, 2021, at 8:46 AM, Rob Sargent >> > wrote: >>> On Oct 4, 2021, at 10:22 AM, Israel Brewster >>> > wrote: >>> Guessing the

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

On 10/4/21 10:28 AM, Adrian Klaver wrote: On 10/4/21 10:10 AM, Shaozhong SHI wrote: Hello, Adrian Klaver, What is the robust way to upgrade Pandas? Carefully. The most recent version is 1.3.3, which is approximately 5 versions ahead of where you are now. The big jump is when Pandas went from

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

On 10/4/21 10:10 AM, Shaozhong SHI wrote: Hello, Adrian Klaver, What is the robust way to upgrade Pandas? Carefully. The most recent version is 1.3.3, which is approximately 5 versions ahead of where you are now. The big jump is when Pandas went from 0.25 to 1.0. See docs here: https://pan

Re: Growth planning

On 10/4/21 11:09 AM, Israel Brewster wrote: On Oct 4, 2021, at 8:46 AM, Rob Sargent > wrote: On Oct 4, 2021, at 10:22 AM, Israel Brewster > wrote: Guessing the “sd” is "standard deviation”?  Any chance those stddevs are easily calculab

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

Hello, Adrian Klaver, What is the robust way to upgrade Pandas? Regards, David On Monday, 4 October 2021, Adrian Klaver wrote: > On 10/4/21 9:20 AM, Shaozhong SHI wrote: > >> Hello, Adrian Klaver, >> >> Pandas version is 0.23.0. >> > > The reason the below does not work is method did not show up

Re: Growth planning

> On Oct 4, 2021, at 8:46 AM, Rob Sargent wrote: > >> On Oct 4, 2021, at 10:22 AM, Israel Brewster > > wrote: > Guessing the “sd” is "standard deviation”? Any chance those stddevs are > easily calculable from base data? Could cut your table size in half (and put

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

On 10/4/21 9:20 AM, Shaozhong SHI wrote: Hello, Adrian Klaver, Pandas version is 0.23.0. The reason the below does not work is method did not show up until pandas 0.24.0. I used the following code: def psql_insert_copy(table, conn, keys, data_iter):     # gets a DBAPI connection that ca

Re: Growth planning

> On Oct 4, 2021, at 10:22 AM, Israel Brewster wrote: > > > - and the data table definition: > > Column | Type | Collation | Nullable | > Default > +--+---+--+--

DELETE ... USING LATERAL

Is it intentional that LATERAL elements in a USING clause of a DELETE statement can't reference the table declared in the FROM clause? Here's a somewhat contrived example. Suppose I have a table with one jsonb column: create table int_arrays (int_array jsonb); insert into int_arrays value

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

> On Oct 4, 2021, at 10:20 AM, Shaozhong SHI wrote: > > Hello, Adrian Klaver, > > Pandas version is 0.23.0. > > I used the following code: > > def psql_insert_copy(table, conn, keys, data_iter): > # gets a DBAPI connection that can provide a cursor > dbapi_conn = conn.connection >

Growth planning

A couple of months ago I was tasked with setting up a new database to hold the results of some new data processing scripts a colleague of my was developing. As I knew this would be a decent amount of data, I did my best to set up a system that would perform well, assigning the machine 20 process

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

Hello, Adrian Klaver, Pandas version is 0.23.0. I used the following code: def psql_insert_copy(table, conn, keys, data_iter): # gets a DBAPI connection that can provide a cursor dbapi_conn = conn.connection with dbapi_conn.cursor() as cur: s_buf = StringIO() writer =

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

On 10/4/21 8:44 AM, Shaozhong SHI wrote: Has anyone tested this one? A Fast Method to Bulk Insert a Pandas DataFrame into Postgres · Ellis Valentiner I tried psql_insert_copy method, but I got the fo

Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

Has anyone tested this one? A Fast Method to Bulk Insert a Pandas DataFrame into Postgres · Ellis Valentiner I tried psql_insert_copy method, but I got the following error message. to_sql() got an unexpec

Re: Problem in Storing Satellite imagey(Geotiff file)

On 10/4/21 2:19 AM, Vijay Garg wrote: Hi Adrian, I am using postgres 13.4 version & postgis 3.1 version. I have used the following command to upload the data. raster2pgsql -I -C -s 4326  C:\Users\Admin\Desktop\dem.tif raster.dem | psql -U postgres -d India -h localhost -p 5432 I am able to

Re: Problem in pg_basebackup of 1.7 TB database while replicating in PostgreSQL 9.4.( Master and slave both are in postgreSQL 9.4)

On 10/4/21 2:28 AM, Amal Chakravarty wrote: Hi all. I am working with a database of 1.7 TB size which is in PostgreSQL 9.4. Kindly suggest how to take the pg_basebackup of such a huge data while replicating from master to slave. Obligatory "9.4 is EOL" comment. -- Angular momentum makes the w

Re: create a new GIN index for my own type

On 10/4/21 3:32 PM, huangning...@yahoo.com wrote: I have already debugged the program according to this step, but I found that in the DataCopy function, the variable typlen should be 8, but it is -1, Well, if you have debugged this, it'd be nice if you could share more information (e.g. bac

Re: Problem in pg_basebackup of 1.7 TB database while replicating in PostgreSQL 9.4.( Master and slave both are in postgreSQL 9.4)

On 10/4/21 9:28 AM, Amal Chakravarty wrote: Hi all. I am working with a database of 1.7 TB size which is in PostgreSQL 9.4. Kindly suggest how to take the pg_basebackup of such a huge data while replicating from master to slave. And what exactly is the issue? Does it fail in some way or are

Re: create a new GIN index for my own type

On 10/4/21 8:30 AM, huangning...@yahoo.com wrote: Hi: I created a new data type, and then I wanted to create a GIN index for it, but when I created the index, the program would crash 。 The version of postgresql is 9.6。 The following is part of the code, and I also refer to the code of intarray

Re: How to set up temporary path for starting up psql in any folder?

On Mon, 4 Oct 2021 at 12:40, Shaozhong SHI wrote: > How to set up temporary path for starting up psql in any folder? > I do not want to disrupt existing settings of paths. You will need to provide a lot more details ( like your OS, and wheter you just want to open the "psql" binary or modify the

Re: PostgreSQL - Ordering Table based of Foreign Key

Hello, Thx for all the feedback. After googling that, I saw this link that can be a way I can go. https://stackoverflow.com/questions/51279588/sort-tables-in-order-of-dependency-postgres Le dim. 3 oct. 2021

Re: Why would Postgres 11 suddenly ignore the recovery.conf file?

On 10/4/21 12:35, Dennis Jacobfeuerborn wrote: Hi, I just stopped and restarted a Postgres 11 docker container which so far has happily streamed the WAL from its primary but after the restart it now seems to completely ignore the recovery.conf file and just outputs this:  2021-10-04 10:14:1

Re: Why would Postgres 11 suddenly ignore the recovery.conf file?

On Mon, Oct 04, 2021 at 12:35:48PM +0200, Dennis Jacobfeuerborn wrote: > Hi, > I just stopped and restarted a Postgres 11 docker container which so far has > happily streamed the WAL from its primary but after the restart it now seems > to completely ignore the recovery.conf file and just outputs t

Re: Why would Postgres 11 suddenly ignore the recovery.conf file?

That happened with Postgres 12 I think so it shouldn't affect this installation. On 10/4/21 12:40, Josef Šimánek wrote: Isn't this related to merge into postgresql.conf (https://www.postgresql.org/docs/current/recovery-config.html)? po 4. 10. 2021 v 12:35 odesílatel Dennis Jacobfeuerborn naps

Re: Why would Postgres 11 suddenly ignore the recovery.conf file?

Isn't this related to merge into postgresql.conf (https://www.postgresql.org/docs/current/recovery-config.html)? po 4. 10. 2021 v 12:35 odesílatel Dennis Jacobfeuerborn napsal: > > Hi, > I just stopped and restarted a Postgres 11 docker container which so far > has happily streamed the WAL from i

How to set up temporary path for starting up psql in any folder?

How to set up temporary path for starting up psql in any folder? I do not want to disrupt existing settings of paths. Regards, David

Why would Postgres 11 suddenly ignore the recovery.conf file?

Hi, I just stopped and restarted a Postgres 11 docker container which so far has happily streamed the WAL from its primary but after the restart it now seems to completely ignore the recovery.conf file and just outputs this: 2021-10-04 10:14:19.103 UTC [1] LOG: listening on IPv4 address "0.

Problem in pg_basebackup of 1.7 TB database while replicating in PostgreSQL 9.4.( Master and slave both are in postgreSQL 9.4)

Hi all. I am working with a database of 1.7 TB size which is in PostgreSQL 9.4. Kindly suggest how to take the pg_basebackup of such a huge data while replicating from master to slave. Regards, Amal Chakravarty.

create a new GIN index for my own type

Hi:  I created a new data type, and then I wanted to create a GIN index for it, but when I created the index, the program would crash 。  The version of postgresql is 9.6。 The following is part of the code, and I also refer to the code of intarray. ```sqlCREATE OR REPLACE FUNCTION geomgrid_in(cst