Re: Get the table creation DDL

On 7/11/22 06:31, Mladen Gogala wrote: On 7/10/22 22:52, Rob Sargent wrote: Are you alone or on a team? What are your pronouns? This did make me chuckle, but no I am just asking whether or not the OP is currently part of a team.

Re: Get the table creation DDL

> On Jul 11, 2022, at 6:46 PM, Igor Korot wrote: > > Hi, > >> On Mon, Jul 11, 2022 at 7:56 AM Rob Sargent wrote: >> >>> On 7/11/22 06:31, Mladen Gogala wrote: >>> On 7/10/22 22:52, Rob Sargent wrote: >>>> Are you alone or on a team

Re: equivalent thing of mtr in mysql

On 7/12/22 12:57, Peter J. Holzer wrote: On 2022-07-12 22:39:31 +0800, merryok wrote: Hi, guys. I'm new here. I'm eager to figure out what is the equivalent thing of mtr in mysql in PG. What is MTR? A search for "mtr mysql" yields "mysql test run" and "multi-threaded replication", neither of w

Re: Batch process

On 7/20/22 09:26, Rama Krishnan wrote: Hi Adrian, Thanks for the update. Is it possible to achieve 1 records deletion on every iteration because my original table contains 5M records during the deletion process it consumes more cpu and resources. See here

Re: Paging through table one row at a ttime

> On Jul 22, 2022, at 6:31 AM, H wrote: > > On 07/20/2022 03:09 PM, H wrote: >> I am running postgres 13 under CentOS 7. I have a need to be able to page >> through a table one row at a time, possibly using pspg (or other tool) >> allowing me to move in either direction in the table one sin

Re: Connecting to postgres on OSX from Swift using PostgresClientKit

On 7/31/22 16:40, howardn...@selestial.com wrote: Hi, Wondered if anyone had experience connection to pg14.1 running on OSX using the swift library PostgresClientKit. I am having difficulties connecting and hopefully someone can put me on the right track. The following is the swift code I

Re: Copying records from TABLE_A to TABLE_B (in the same database)

On 8/2/22 12:37, Ron wrote: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are:     INSERT INTO table_a SELECT * FROM table_b; and     \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);     \COPY table_b FROM '/tmp/

Re: Copying records from TABLE_A to TABLE_B (in the same database)

On 8/2/22 12:51, Thomas Kellerer wrote: Ron schrieb am 02.08.2022 um 20:37: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are: INSERT INTO table_a SELECT * FROM table_b; and \COPY table_a TO '/tmp/table_a.tsv'

Re: Copying records from TABLE_A to TABLE_B (in the same database)

Logical replication might be another option. Although I am not sure if that is even possible inside the samme database. I know it's tricky inside the same server (between different databases) If you can get outside sql, the bulk copy facilities (CopyManager in java) is blindingly fast for m

Re: Creating A GIN index on JSONB column (large database)

> On Aug 9, 2022, at 7:04 AM, Taylor Smith wrote: > > Hi all, > > I have a database that is in excess of 20TB in size, partitioned by date on a > month to month basis. > > There is a column within that stores text (validated to be json but not > stored as JSONB). I have a requirement to ma

Re: Creating A GIN index on JSONB column (large database)

> On Aug 9, 2022, at 7:45 AM, Taylor Smith wrote: > > Thanks Rob, > > This is what I was thinking. Would you say it presents any risk then having a > database unable to rebuild its own indexes if needed? > > No I would not. The scale-up + rebuild should tell you how much you actually ne

Re: Modelling a web CMS in Postgres ... a little advice needed

> On Aug 11, 2022, at 5:30 AM, Laura Smith > wrote: > > > > > > > Sent with Proton Mail secure email. > > --- Original Message --- > On Thursday, August 11th, 2022 at 13:11, Ray O'Donnell > wrote: > > >> On 11/08/2022 11:00, Laura Smith wrote: >> >>> Hi Tony >>> >>> The re

Re: ***SPAM*** Re: Can I get the number of results plus the results with a single query?

On 8/15/22 14:37, Perry Smith wrote: On Aug 15, 2022, at 08:55, David G. Johnston wrote: On Monday, August 15, 2022, Perry Smith wrote: I’ve been toying with row_number() and then sort by row_number descending and pick off the first row as the total number. Use count as a window

Re: massive update on gin index

On 9/14/22 13:38, Guyren Howe wrote: You might consider defining a phone type that includes your “type” information, and just having an array of those, if you really want to do something like this. But a related table instead would be the obvious answer. Did you try a simple array of phone n

tcp settings

I'm wondering if there's a disconnect between my servers postgresql.conf and the system tcp settings? Are the config names supposed to match a file in /proc/sys/net/ipv4? In postgres 14 install's postgresql.conf on centos 7: # - TCP settings - # see "man tcp" for details #tcp_keepal

Re: tcp settings

>> My issue is that psql is timing out with "SSL SYSCALL error: Connection >> timed out". > > then keepalives aren't necessarily the solution anyway. When is > this failure occurring ... is it while trying to establish the > database connection in the first place? Or does it only happen > if

Re: I slipped up so that no existing role allows connection. Is rescue possible?

> On Sep 20, 2022, at 4:54 PM, Theodore M Rolle, Jr. wrote: > >  > . > . > . > And Tom’s English is excellent! >> That’s what this is! With the bonus of AK,DJ and the gang - and you Mladen. (Maybe not as searchable as one might like but that makes one pay attention. )

Re: [EXT] pg_stat_activity.backend_xmin

> On Sep 21, 2022, at 9:32 AM, Dirschel, Steve > wrote: > > On Wed, 2022-09-21 at 14:11 +, Dirschel, Steve wrote: >>> We are troubleshooting an issue where autovacuum is not cleaning up a table. >>> The application using this database runs with autocommit turned off. >>> We can see in pg_

Re: tcp settings

> On Sep 20, 2022, at 10:03 PM, Tom Lane wrote: > > Rob Sargent writes: >>> then keepalives aren't necessarily the solution anyway. When is >>> this failure occurring ... is it while trying to establish the >>> database connection in the first pla

Re: NULL values and Java JDBC

On 9/30/22 09:46, Matthias Apitz wrote: Hello, Columns may contain NULL values. The ecpg for pre-compiling ESQL/C code has an option to let return NULL values in CHAR columns as empty strings "" and INTEGER as INT_MIN (-0x7fff - 1) values. Is there a similar option for Java JDBC? Thanks

Re: could not find shared library for Python

> On Oct 17, 2022, at 8:07 AM, jacktby wrote: > > > > I use CentOS7 and upgrade python2.7 to python3.7, but it gives me an error > "could not find shared library for Python",I use the newest code from github > repo, how should I do? > > jacktby > jack...@gmail.com > >

Re: Does standalone postfrsql have autogrowth/ manual growth on table space?

On 10/20/22 11:29, Ron wrote: On 10/20/22 12:19, Vince McMahon wrote: In other databases, there is a way to preallocate the table space to allow bulk loading of data in a well packed and continuous space. Does psql have that auto/manual growth? There's no pre-allocation in Postgresql.  When

Re: please give me select sqls examples to distinct these!

> On Oct 25, 2022, at 7:55 AM, jack...@gmail.com wrote: > >  > > typedef enum SetOperation > { > SETOP_NONE = 0, > SETOP_UNION, > SETOP_INTERSECT, > SETOP_EXCEPT > } SetOperation; > jack...@gmail.com Please use just text. What ‘dialect’ are using? In Postgres 0: select * from

Re: please give me select sqls examples to distinct these!

On 10/25/22 09:24, David G. Johnston wrote: On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston wrote: On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent wrote: 2: select * from table join table b on Id = idb #2 is probably conceptually correct but in this context should be

Re: please give me select sqls examples to distinct these!

On 10/25/22 09:58, David G. Johnston wrote: On Tue, Oct 25, 2022 at 8:56 AM Rob Sargent wrote: On 10/25/22 09:24, David G. Johnston wrote: On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston wrote: On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent wrote

Re: How to know how much CPU, RAM is used by existing 1 database

On 10/26/22 08:26, Yi Sun wrote: On Wed, 26 Oct 2022 at 18:10, jian he wrote: On Wed, Oct 26, 2022 at 11:07 AM Yi Sun wrote: Hi Guys, Who can help me with this please? I researched but still no result yet, thank you On Tue, 25 Oct 2022 at 16:30, Yi Su

Re: Delete a table automatic?

On 11/1/22 03:31, jian he wrote: On Tue, Nov 1, 2022 at 2:33 PM 黄宁 wrote: I now have two tables named A and B. Table B is calculated based on the data of table A. I wonder if table B can be automatically deleted when table A is deleted？ Your question seems not that specific. You

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

 "Additionally, while reading the next chapter, Server Setup and Operation, is recommended if you are using a binary package the setup and operational environment it creates is likely to be somewhat different than what is described in this documentation.  Please read the documentation for the

Re: copy file from a client app to remote postgres isntance

On 11/7/22 09:57, Вадим Самохин wrote: Hi all, I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020 ). It boils down to spec

Re: copy file from a client app to remote postgres isntance

On 11/7/22 10:51, Вадим Самохин wrote: Well, actually, just ordinary 3 tier architecture. Simple UI connected via restful API with backend written in php, which copies some data in a remote database, that's pretty much it. пн, 7 нояб. 2022 г. в 20:30, Rob Sargent : On 11/7/22

Re: Setting up replication on Windows, v9.4

On 11/7/22 13:59, Brad White wrote: > v9.4 has been EOL for 2 years 9 months. As I said, the next step will be to upgrade. It would make sense to upgrade first, since "there have been some big advances since then which make replication much easier" But when we upgraded, the app stopped w

reviving "custom" dump

Short version: Does a current version of postgres tolerate ascii dumps from older versions? I've been charged with reviving an old project.  I have a dump dated July of 2021 which file tells me is a "PostgreSQL custom database dump V1.13-0".  If our compute centre won't roll me a V12(?) postgr

Re: reviving "custom" dump

On 11/10/22 23:29, Tom Lane wrote: Rob Sargent writes: Short version: Does a current version of postgres tolerate ascii dumps from older versions? We intend it to. Have you got an actual problem? regards, tom lane I have to lay out options in the morning.  Thanks

Re: reviving "custom" dump

On 11/10/22 23:29, David G. Johnston wrote: On Thu, Nov 10, 2022 at 11:13 PM Rob Sargent wrote: Short version: Does a current version of postgres tolerate ascii dumps from older versions? I've been charged with reviving an old project.  I have a dump dated July of 2021

Re: An I/O error occured while sending to the backend

On 11/16/22 00:37, gzh wrote: Thank you very much for your advice. What I don't understand is that there is no problem when executing ① alone or ② alone, the error occurs when ① and ② are executed together . It works well when i let the application sleep after ① for 10 seconds before executin

Re: collect2: error: ld returned 1 exit status

> On Nov 26, 2022, at 3:43 PM, William Torrez Corea > wrote: > >  > I am using libpq: the C application programmer's interface to PostgreSQL. > > Compile the code: > >> sudo cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq > > But i get the following error: > >> collect

Re: collect2: error: ld returned 1 exit status

On Nov 26, 2022, at 4:17 PM, William Torrez Corea wrote:On Sat, Nov 26, 2022 at 5:00 PM Rob Sargent <robjsarg...@gmail.com> wrote:On Nov 26, 2022, at 3:43 PM, William Torrez Corea <willitc9...@gmail.com> wrote:I am using libpq: the C application programmer's interface to Postgr

Re: collect2: error: ld returned 1 exit status

header files for libpq5 (PostgreSQL library), the package is broken. -- With kindest regards, William.⢀⣴⠾⠻⢶⣦⠀ ⣾⠁⢠⠒⠀⣿⡁ Debian - The universal operating system⢿⡄⠘⠷⠚⠋⠀ https://www.debian.org⠈⠳⣄ I’m confused. Is that  ‘package is broken’ coming from dpkg. 

Re: integer square root function proposed

On 12/17/22 19:39, Martin L. Buchanan wrote: Dear PostgreSQL colleagues: I have just joined this, my first PG mailing list. Reading the documentation I found no built-in function for integer square root, requiring a sequence of: floor(sqrt(foo))::integer to go from an integer to the integer

Re: integer square root function proposed

On 12/17/22 20:40, Martin L. Buchanan wrote: Dear Rob and all readers: Generating prime numbers is one example where you use integer square root in the inner loop, going from integer to integer. Calculating an integer square root from an integer input may have a more efficient algorithm than

Re: PostgreSQL 12 service failing in Ubuntu 20.04 after a few hours

On 1/1/23 14:48, Adrian Klaver wrote: On 1/1/23 13:11, Antonis Christodoulou wrote: Hello Adrian, No it’s not open, but the database itself has very simple credentials (I am just starting with PostgreSQL). What’s weird about the logs? Not the logs the ps output. I would expect to see somethi

Re: What is the best setup for distributed and fault-tolerant PG database?

On 1/4/23 06:26, Age Apache wrote: Dear PG experts, I am new to postgres, and I am also not a DBA. I am a solo developer who is trying to evaluate what database to use for my hybrid multi-tenancy sub-apps i.e. users of the application will be authorised to use part or whole of the application

Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"

On 1/16/23 14:18, Ron wrote: On 1/16/23 07:11, Laurenz Albe wrote: On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote: This is a puzzle I have not been able to crack yet. We have a single-page table with 28 rows that is purely read-only. There isn't a way in postgres to make a table RO, but

Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?

On 1/18/23 09:38, HECTOR INGERTO wrote: I wanted to understand the underlying issue. I use ZFS snapshots instead of a “correct” backup because with only two machines it allows me to have backups in the main machine and in the secondary too that acts as hotspare at the same time. To accompli

Re: Tools for moving normalized data around

On 1/18/23 13:15, Gavan Schneider wrote: On 19 Jan 2023, at 6:47, Peter wrote: Now I want to grab some part of the data, on a certain condition (let's say all records belonging to user 'Bob', if there is a "user" table somewhere at the tree-bottom), and move it to another database with the very

Re: Sequence vs UUID

On 1/26/23 14:36, Merlin Moncure wrote: On Thu, Jan 26, 2023 at 1:18 PM veem v wrote: Hello, We were trying to understand whether we should use UUID or Sequence in general for primary keys. In many of the blogs(one is below) across multiple databases, I saw over the internet and all

Re: Sequence vs UUID

> So forget about performance issues (there will ALWAYS be need for faster > systems). The ease and functionality with UUID > is so mutch better. Sequence keys are a terrible idea! > > // GH > Wow. I am not alone >

nextval per counted

I'm trying to craft SQL to invoke a sequence nextval once per grouped value. So far I have this: with husb as( select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) as mates from emp_all_by3 e group by e.ma order by mates ) select mates, count(*) from husb

Re: nextval per counted

On 1/27/23 14:20, David G. Johnston wrote: On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent wrote: I'm trying to craft SQL to invoke a sequence nextval once per grouped value. This seems like a very unusual usage of nextval/sequences... with cleanup as (   select DISTINCT e.ma

Re: nextval per counted

On 1/27/23 14:31, David G. Johnston wrote: On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent wrote: On 1/27/23 14:20, David G. Johnston wrote: On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent wrote: I'm trying to craft SQL to invoke a sequence nextval once per grouped

Re: Sequence vs UUID

> On Feb 2, 2023, at 1:26 PM, Benedict Holland > wrote: > >  > No idea at all. We had the data for the insert and had to insert it again. It > was extremely confusing but oh boy did it wreck our systems. > > Thanks, > Ben Someone has a baked-in uuid in a script I suspect. >

Re: Quoting issue from ODBC

On 2/7/23 17:23, David G. Johnston wrote: On Tue, Feb 7, 2023 at 5:20 PM Brad White wrote: For example, this is the literal code in VBA Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable & "].[InsertFlag] = Null" _     & " WHERE ((([" & strTable & "].[Inser

psql "\d" no longer working

Seems I've lost the table definition meta-command riftehr=> \d actual_and_inf_rel_clean_final ERROR:  column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi... while listing tables still works riftehr=> \dt act* List of rela

Re: psql "\d" no longer working

I doubt that is the problem as the issue is the column in the table not finding the table. pg_class.relhasoids no longer exists 12+, so the post from Georg is probably pointing in the right direction. Ah, yes.  My client machine at compute centre has to be told to put version 14 on the p

Re: HOWTO? Permissions for user to access a single db

On 2/13/23 16:14, Damian Carey wrote: Thx Tom Fine advice that I will follow up. One tiny thing without wasting (too much) more of your time. In the working "promiscuous" version they get access the VPS as the same linux user that my product is running on, and superuser PG access. In the fa

Re: HOWTO? Permissions for user to access a single db

On 2/13/23 21:35, Damian Carey wrote: Tom, Rob & Adrian, I understand exactly what each of you are getting at, but instead of fumbling and further wasting your time I'm going to get a freelancer to smash out a suitable setup sans beginner mistakes. It's a pretty basic problem for a learned co

pro services list

Is this the place for suggestions for postgres.org? I had occasion yesterday to visit the page of available support companies.  I see it's sorted alphabetically.  Does that tend to favour the "A"s?  Thinking of Yellow Page (tangible, phone company version) listings like " knife sharpening"

Re: Query plan for "id IS NULL" on PK

On 2/14/23 15:04, Ben Chrobot wrote: Hello, Long time listener, first time caller. We have a large table (~470 million rows) with integer primary key id (not null) on a Postgres 14.5 cluster. A third-party tool is attempting to perform a SELECT-based full table copy in preparation for log-ba

Re: Query plan for "id IS NULL" on PK

On 2/14/23 15:30, David G. Johnston wrote: On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent wrote: When will id be null in a primary key? The OP seems to be aware of this... "We cannot change the query being executed. Is there any way we can make the query planner ignore `OR (id IS

Re: Query plan for "id IS NULL" on PK

On 2/14/23 15:43, Peter J. Holzer wrote: On 2023-02-14 15:36:32 -0700, Rob Sargent wrote: But if the query is supposed to be generic and re-used in a situation where id could be null, wouldn't the null id records be fetched every time? No, they will never be fetched because of the AND

curiosity in default column header

riftehr=> select ascii(substring('sadb', 2,1));  ascii ---     97 (1 row) riftehr=> select 24::bit(8);    bit --  00011000 (1 row) riftehr=> select ascii(substring('sadb', 2,1))::bit(8);   ascii --  0111 (1 row) Why is the last one headed "ascii" and not "bit"?

Re: Quit currently running query

On 2/28/23 03:53, Albert Cornelius wrote: How can I quit a currently running query? I've issued a query and my server does not respond anymore. Is there another solution than using kill -9? can you open another psql session to your server?

Re: Converting row elements into a arrays?

On 3/2/23 13:58, Ron wrote: Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.  f1 | f2_array +- 1 | {1,2,3}   2 | {1,2,3,4}   3 | {1,2} The ultimate goal is to somehow

Re: Converting row elements into a arrays?

On 3/2/23 14:49, Ron wrote: On 3/2/23 15:45, Rob Sargent wrote: On 3/2/23 13:58, Ron wrote: Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.  f1 | f2_array +-

psql \copy with multi-line query

Using version 13 psql on version 14 server and having trouble sending a multi-line select in the \copy.  I don't see any remark about this in the man page. \copy ( select a.mrn, a.relationship,a.relation_mrn,a.provided_relationship from actual_and_inf_rel_part1_unique_clean a join family_ids f

Re: psql \copy with multi-line query

On 3/24/23 17:14, David G. Johnston wrote: On Fri, Mar 24, 2023 at 4:04 PM Rob Sargent wrote: Using version 13 psql on version 14 server and having trouble sending a multi-line select in the \copy.  I don't see any remark about this in the man page. The copy meta-command c

Re: Cluster table based on grand parent?

On 3/28/23 10:28, Dominique Devienne wrote: On Tue, Mar 28, 2023 at 6:06 PM Ron wrote: You can only get from parent to grandchild via//child.id to grandchild.parent, so why not cluster grandchild on grandchild.parent? Hi. I don't understand your question. Yes, of

Re: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres

On 4/9/23 19:55, Louis Tian wrote: Hi Alban, "I am not expecting an error here", by "here" I means when doing a TRUE UPSERT (an upsert current does not exist in Postgres). I am NOT referring to an "Insert on conflict do update" (which despite its intention and wide acceptance is not fully equi

Re: TEXT column > 1Gb

On 4/11/23 11:41, Joe Carlson wrote: Hello, I’ve recently encountered the issue of trying to insert more than 1 Gb into a TEXT column. While the docs say TEXT is unlimited length, I had been unaware of the 1Gb buffer size limitations. We can debate whether or not saving something this big in

Re: Guidance on INSERT RETURNING order

On 4/11/23 14:37, Federico wrote: The problem here is not having the auto increment id in a particular order, is that there is apparently no correlation with the position of an element in the values clause with the id generated. That's the reason for using the sentinel column in the general solu

Re: Guidance on INSERT RETURNING order

Can your client retain a hashmap of md5,data pairings, allowing the lookup on the way back using the returned data and supplied id? When using unique columns or similar, that's something that is done, but if there are no unique columns in the value no match can be done reliably with the sourc

Re: TEXT column > 1Gb

On 4/12/23 08:59, Joe Carlson wrote: I’m curious what you learned. I’ve been tripping over the buffer allocation issue when either splitting input text into chunks or aggregating chunks in selects. I’ve decided that I need to move this to client side. The use case is genomics. Extracting subs

Re: TEXT column > 1Gb

On 4/12/23 11:24, Benedict Holland wrote: For documents that long I would seriously consider using large objects and refencing them with their OIDs. Text fields get put in a special location within the database. It's similar (possibly exactly) to using large objects. Also, you can potentially c

Re: TEXT column > 1Gb

On 4/12/23 13:02, Ron wrote: /Must/ the genome all be in one big file, or can you store them one line per table row? Not sure what OP is doing with plant genomes (other than some genomics) but the tools all use files and pipeline of sub-tools.  In and out of tuples would be expensive.  Very,v

Re: TEXT column > 1Gb

On 4/12/23 15:03, Joe Carlson wrote: On Apr 12, 2023, at 12:21 PM, Rob Sargent wrote: On 4/12/23 13:02, Ron wrote: /Must/ the genome all be in one big file, or can you store them one line per table row? The assumption in the schema I’m using is 1 chromosome per record. Chromosomes are

Re: Guidance on INSERT RETURNING order

This would be a nice solution… but the PK could be always generated, or not even sequential (UUIDs for example). If you’re developing schema-first the application would not even (need to) know about how the IDs are generated as it’s the DB that generates them. AIUI the OP’s an SQLAlchemy devel

Re: Redirecting select() output generates error

> On Oct 29, 2018, at 1:37 PM, David G. Johnston > wrote: > > On Mon, Oct 29, 2018 at 12:30 PM Rich Shepard > wrote: > willamette-river-hg-# \out data-summary-by-form.txt > willamette-river-hg-# select param, site_nbr, sampdate, min(quant), > max(quant), unit

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 11/5/18 7:05 PM, Ron wrote: I've got a very puzzling problem on 9.6.6 systems we just migrated from 8.4.  (The same problem happened on 9.6.9, but rolled it back so as to make prod have the same version as our Staging systems.) We've got a giant script full of DROP TRIGGER IF EXISTS and CR

Re: Is pg_restore in 10.6 working?

On 11/12/18 11:44 AM, Tom Lane wrote: David writes: I have some experience with different versions of Postgres, but I'm just getting around to using pg_restore, and it's not working for me at all. ... But a matching pg_restore command does nothing. pg_restore -U postgres -f predata.sql -v Th

Re: Is pg_restore in 10.6 working?

2018 at 2:28 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: On 11/12/18 11:44 AM, Tom Lane wrote: > David mailto:dlbarro...@gmail.com>> writes: >> I have some experience with different versions of Postgres, but I'm just >> getting

Re: Is pg_restore in 10.6 working?

On 11/12/18 2:06 PM, Tom Lane wrote: Rob Sargent writes: On 11/12/18 12:39 PM, David wrote: I'm not following your question.  The pre-data and post-data sections each go to an individual file, but the data section goes to a directory.  I can restore the files using psql, but it is the re

Re: Java UnsatisfiedLinkError exception when connecting to Postgresql database

On 11/14/18 5:03 PM, dcl...@cinci.rr.com wrote: Adrian Klaver wrote: On 11/14/18 10:24 AM, dcl...@cinci.rr.com wrote: Please reply to list also. Ccing list. Adrian Klaver wrote: On 11/14/18 9:25 AM, dcl...@cinci.rr.com wrote: Hello; I've written a Java program which uses Postgr

Re: Vacuum and Materialized view refresh slow

On 11/30/18 1:53 PM, Rene Romero Benavides wrote: Have you benchmarked the new infrastructure's IO and network performance? why did you switch providers? was it because of cost? And have you proven to yourself that all the indices are in place, stats are up-to-date?  You didn't by any chance mi

Re: simple division

On 12/4/18 2:36 PM, Martin Mueller wrote: It worked, and I must have done something wrong. I'm probably not the only person who would find something like the following helpful: division (integer division truncates the result)10/33 The math types might take offense here, with th

Re: Moving large table between servers: logical replication or postgres_fdw

> On Dec 4, 2018, at 8:14 PM, Rhys A.D. Stewart wrote: > > Greetings Folks, > > I have a relatively large table (100m rows) that I want to move to a > new box with more resources. The table isn't doing anything...i.e its > not being updated or read from. Which approach would be faster to move

Re: simple division

> On Dec 4, 2018, at 9:33 PM, Gavin Flower > wrote: > >> On 05/12/2018 10:51, Rob Sargent wrote: >> >>> On 12/4/18 2:36 PM, Martin Mueller wrote: >>> It worked, and I must have done something wrong. I'm probably not the only >>> perso

Re: Moving large table between servers: logical replication or postgres_fdw

L > On Dec 4, 2018, at 11:13 PM, Rene Romero Benavides > wrote: > > I tend to believe that a backup (pg_dump) in custom format (-F c) using > multiple jobs (parallel) -> restore (pg_restore) also with multiple > concurrent jobs would be better. > >> Am Di., 4. Dez. 2018 um 21:14 Uhr schrieb

Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)

> On Dec 8, 2018, at 3:12 PM, Andres Freund wrote: > > On 2018-12-08 12:06:23 -0800, Jeremy Schneider wrote: >> On RDS PostgreSQL, the default is 25% of your server memory. This seems >> to be pretty widely accepted as a good starting point on PostgreSQL. > > FWIW, I think it's widely cited,

loading jdbc Driver in servlet

Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9 It appears to me that I need to make the call "Class.forName("org.postgresql.Driver)" when the entry is in a servlet.  Is this expected, within a servlet, or is this just /post hoc ergo propter hoc /at it finest and I changed something e

Re: loading jdbc Driver in servlet

ntl.com <http://www.postgresintl.com/> > > On Fri, 14 Dec 2018 at 13:29, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9 > > It appears to me that I need to make the call > "Class.forName(&qu

Re: loading jdbc Driver in servlet

> On Dec 14, 2018, at 2:02 PM, Rob Sargent wrote: > > > >> On Dec 14, 2018, at 1:30 PM, Dave Cramer > <mailto:p...@fastcrypt.com>> wrote: >> >> Strange, I wouldn't think so, but then I haven't used a raw servlet for so >

Re: loading jdbc Driver in servlet

> > >> On Fri, 14 Dec 2018 at 16:04, Rob Sargent wrote: >> >> >>>> On Dec 14, 2018, at 2:02 PM, Rob Sargent wrote: >>>> >>>> >>>> >>>> On Dec 14, 2018, at 1:30 PM, Dave Cramer wrote: >

Re: loading jdbc Driver in servlet

> On Dec 16, 2018, at 12:12 PM, Dave Cramer wrote: > > So you are starting up tomcat yourself ? Perhaps that is the difference ? > I have no idea what the tomcat wrapper does, but I'd be curious if the same > thing happens when stared normally > > Dave Cramer > Whatever the is the differenc

Re: loading jdbc Driver in servlet

> On Dec 16, 2018, at 5:55 PM, Martin Gainty wrote: > > 99% of the problems with 'isolated classloader' are solved using the class > e.g. > Granted. But this seems to fly in the face of both Postgres docs and modern JDKs.

Re: Watching for view changes

> On Dec 20, 2018, at 1:04 PM, Mitar wrote: > > Hi! > > Ah, that was a thread about table changes. I am interested in data > changes (results from a query). > > Thanks. > Are you hoping to see the difference in the returned values for successive calls to the same query?

Re: Watching for view changes

Is this of theoretical interest (up to and including a specification/requirement) or this a practical concern (i.e. need to know when to update somebody’s dashboard widget (but the query is too slow to simply refresh on-demand)? > On Dec 22, 2018, at 9:42 AM, Ricardo Martin Gomez > wrote: >

Re: Can't quote_literal with COPY FROM PROGRAM

> On Dec 31, 2018, at 10:36 AM, Mark Mikulec wrote: > > Hi, > > This command, which generates a JSON object as output, has some escaped data > with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS) > > C:\\Portable\\curl\\curl.exe -k > "https://maps.googleapis.com/maps/api/dire

Re: Relocatable Binaries (RPMs) : custom installation path for PostgreSQL

Do Options and One-Click seem oxymoronic to anyone else? > On Jan 1, 2019, at 5:18 PM, chiru r wrote: > > Yes, at this moment we are not considering symlinks. > > We would like to have the options in RPMs itself. > >> On Tue, Jan 1, 2019 at 3:53 PM Brent Wood wrote: >> Have you considered sym

Re: Use bytearray for blobs or not?

On 1/4/19 4:48 AM, Achilleas Mantzios wrote: On 4/1/19 1:41 μ.μ., Thomas Güttler wrote: Some months ago I wrote a little application with Python+Django which stores blob data in bytearrays. It works. In the future there will be a lot more traffic, and I am unsure if this is really a good so

Re: Comparing dates in DDL

On 1/4/19 10:12 AM, Igor Korot wrote: Hi, Rich, On Fri, Jan 4, 2019 at 10:53 AM Rich Shepard wrote: I have a projects table that includes these two columns: start_date date DEFAULT CURRENT_DATE, end_date date CONSTRAINT valid_start_date CHECK (start_date <= end_date),

<    1   2   3   4   5   6   7   >