Re: Add columns to table; insert values based on row

On Thu, Nov 1, 2018 at 5:08 PM Rich Shepard wrote: > On Thu, 1 Nov 2018, Rich Shepard wrote: > > > I'll use gawk to extract the relevant fields from the text file in which > > they reside (in the correct order), then use emacs keyboard macros to add > > the appropriate update text to each line. M

Re: Truncation of UNLOGGED tables upon restart.

Greetings, * Michael Paquier (mich...@paquier.xyz) wrote: > On Thu, Nov 01, 2018 at 07:06:32PM -0400, Stephen Frost wrote: > > No, we don't currently track that information but it's an interesting > > idea, at least imv. > > What would be the use case for it? What you are looking for here is > g

Re: Truncation of UNLOGGED tables upon restart.

On Thu, Nov 01, 2018 at 07:06:32PM -0400, Stephen Frost wrote: > No, we don't currently track that information but it's an interesting > idea, at least imv. What would be the use case for it? What you are looking for here is gathering information about all pages in a relation and just aggregate w

Re: Add columns to table; insert values based on row

On Thu, 1 Nov 2018, Rich Shepard wrote: I'll use gawk to extract the relevant fields from the text file in which they reside (in the correct order), then use emacs keyboard macros to add the appropriate update text to each line. Must more efficient (and less tedious) than manually writing each l

Re: Truncation of UNLOGGED tables upon restart.

Greetings, * Ravi Krishna (srkrish...@aol.com) wrote: > > There is no such thing as a "read only" table in PostgreSQL.  All tables > > are read/write no matter that frequency of either event.  There is nothing > > > inherently special about "no writes for 4 days" and "no writes for 10 > > secon

Re: Add columns to table; insert values based on row

On Fri, 2 Nov 2018, Tim Cross wrote: 3. Is it really insert or update you need? Tim, Update. It's been a long time since I did any database manipulation (I just use the existing databases as each project needs its data) so I sometimes make these types of mistakes. Your idea of manipul

Re: Truncation of UNLOGGED tables upon restart.

On 11/01/2018 03:58 PM, David G. Johnston wrote: On Thu, Nov 1, 2018 at 1:49 PM Ravi Krishna > wrote: Per documentation unlogged tables are not crash safe and PG will truncate it when it restarts after a crash. Does this apply to even read only unlogged tab

Re: Add columns to table; insert values based on row

Rich Shepard writes: > On Thu, 1 Nov 2018, Adrian Klaver wrote: > >>> alter table stations add column start_date date; >>> alter table stations add column end_date date; >>> alter table stations add column howmany integer; >>> alter table stations add column bin_col char(8); >>> >>> insert int

Re: Truncation of UNLOGGED tables upon restart.

> There is no such thing as a "read only" table in PostgreSQL.  All tables are > read/write no matter that frequency of either event.  There is nothing > > inherently special about "no writes for 4 days" and "no writes for 10 > seconds" that would allow for a distinction to be made.  There cou

Re: Truncation of UNLOGGED tables upon restart.

On Thu, Nov 1, 2018 at 1:49 PM Ravi Krishna wrote: > Per documentation unlogged tables are not crash safe and PG will truncate > it when it restarts after a crash. Does this apply to even read only > unlogged tables. > > For example: > > On Monday I load data into unlogged tables. > > Then from T

Re: Add columns to table; insert values based on row

On Thu, 1 Nov 2018, Adrian Klaver wrote: Why? As David said that will get you an additional 82 rows with data that is dissociated from the existing data. You want to update the existing rows with the new field data for each station. D'oh! Of course! Thanks both, Rich

Re: Add columns to table; insert values based on row

On 11/1/18 1:53 PM, Rich Shepard wrote: On Thu, 1 Nov 2018, David G. Johnston wrote: That makes no sense to me...you already have 82 rows on the table so if you insert 82 more you'll have 164 which doesn't seem like what you would want... I would probably do: David,   The table already ex

Re: Add columns to table; insert values based on row

On Thu, 1 Nov 2018, David G. Johnston wrote: That makes no sense to me...you already have 82 rows on the table so if you insert 82 more you'll have 164 which doesn't seem like what you would want... I would probably do: David, The table already exists with four attribute columns and their

Re: Add columns to table; insert values based on row

On Thu, 1 Nov 2018, Adrian Klaver wrote: alter table stations add column start_date date; alter table stations add column end_date date; alter table stations add column howmany integer; alter table stations add column bin_col char(8); insert into stations (start_date, end_date, howmany, bin_col

Truncation of UNLOGGED tables upon restart.

Per documentation unlogged tables are not crash safe and PG will truncate it when it restarts after a crash. Does this apply to even read only unlogged tables. For example: On Monday I load data into unlogged tables. Then from Tue onwards the table is only read by application. On Fri morning

Re: Add columns to table; insert values based on row

On 11/1/18 1:23 PM, Rich Shepard wrote:   I have the following code in a script: alter table stations add column start_date date; alter table stations add column end_date date; alter table stations add column howmany integer; alter table stations add column bin_col char(8); insert into station

Re: Add columns to table; insert values based on row

On Thu, Nov 1, 2018 at 1:26 PM Rich Shepard wrote: >I have the following code in a script: > > alter table stations add column start_date date; > alter table stations add column end_date date; > alter table stations add column howmany integer; > alter table stations add column bin_col char(8)

Add columns to table; insert values based on row

I have the following code in a script: alter table stations add column start_date date; alter table stations add column end_date date; alter table stations add column howmany integer; alter table stations add column bin_col char(8); insert into stations (start_date, end_date, howmany, bin_col)

Truncated queries in the pg_log file

Hi, v8.4  (we're migrating to 9.6 tomorrow night, but the work must still proceed today.) SQL statements are being logged (which is good), but only the fist 400 or so bytes is recorded.  What knob do I tweak to record the whole query? Here are the relevant postgres.conf items which are not

Re: Copy data from DB2 (Linux) to PG

On Thu, Nov 1, 2018 at 10:50 AM Ravi Krishna wrote: > [...] What I need is a constant refresh. > We plan to use it daily to replicate data from db2 to pg. Perhaps you've already considered and discarded the idea, but your use case made me think back to when I was looking at AWS SCT as a way to mi

Re: Copy data from DB2 (Linux) to PG

> I've never used it, but there is this in case it's helpful: > https://github.com/dalibo/db2topg/ I looked into it.  I thought it is a schema convertor plus data load.  In other words, it is one of those one time migration script.  What I need is a constant refresh. We plan to use it daily

Re: Copy data from DB2 (Linux) to PG

On Thu, Nov 1, 2018 at 10:28 AM Ravi Krishna wrote: > > I have a project to develop a script/tool to copy data from DB2 to PG. The > approach I am thinking is > > 1. Export data from db2 in a text file, with, say pipe as delimiter. > 2. Load the data from the text file to PG using COPY command.

Copy data from DB2 (Linux) to PG

I have a project to develop a script/tool to copy data from DB2 to PG. The approach I am thinking is 1. Export data from db2 in a text file, with, say pipe as delimiter. 2. Load the data from the text file to PG using COPY command. In order to make it faster I can parallelize export and load wi

Re: rw_redis_fdw: SQL Errors when statement is within a function

Good evening, Thanks for the response. On Wed, Oct 31, 2018, 2:59 PM Adrian Klaver On 10/31/18 2:03 AM, GPT wrote: > > Very good morning, > > > > Thanks very much for your direct, clear and enlightening response! > > > > As regards Q2, and any other dynamic behaviour/feature or whatever PG > > i

Re: backend crash on DELETE, reproducible locally

On Thu, Nov 01, 2018 at 11:43:56AM -0400, Tom Lane wrote: > Alvaro Herrera writes: > > Ah, now this is interesting. Can you please supply the definition of > > the table? I'm wondering if there is a partitioned table with an FK to > > this one. I'm not quite seeing how come 'tup' is NULL there

Re: backend crash on DELETE, reproducible locally

On Thu, Nov 01, 2018 at 12:27:23PM -0300, Alvaro Herrera wrote: > > Program received signal SIGSEGV, Segmentation fault. > > heap_attisnull (tup=0x0, attnum=5, tupleDesc=0xb2990ef4) at > > ./build/../src/backend/access/common/heaptuple.c:403 > > 403 ./build/../src/backend/access/common/heaptuple.

Re: backend crash on DELETE, reproducible locally

Alvaro Herrera writes: > Ah, now this is interesting. Can you please supply the definition of > the table? I'm wondering if there is a partitioned table with an FK to > this one. I'm not quite seeing how come 'tup' is NULL there. Yeah, apparently we've passed a null OLD tuple to an RI_FKey_cas

Re: backend crash on DELETE, reproducible locally

On Thu, Nov 01, 2018 at 12:27:23PM -0300, Alvaro Herrera wrote: > Ah, now this is interesting. Can you please supply the definition of > the table? Attached. > I'm wondering if there is a partitioned table with an FK to > this one. There is. Both ref.auto_hint and clin.suppressed_hint are usin

Re: backend crash on DELETE, reproducible locally

On 2018-Nov-01, Karsten Hilbert wrote: > Program received signal SIGSEGV, Segmentation fault. > heap_attisnull (tup=0x0, attnum=5, tupleDesc=0xb2990ef4) at > ./build/../src/backend/access/common/heaptuple.c:403 > 403 ./build/../src/backend/access/common/heaptuple.c: Datei oder > Verzeichnis ni

Re: backend crash on DELETE, reproducible locally

On Thu, Nov 01, 2018 at 12:27:23PM -0300, Alvaro Herrera wrote: > In general terms, this bug report would have been more actionable if you > had shown the definition of the tables involved right from the start. Sorry for that, will supply. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC8

Re: backend crash on DELETE, reproducible locally

On Thu, Nov 01, 2018 at 04:11:33PM +0100, Karsten Hilbert wrote: >> Will attempt to get a stack trace ... Eventually, the stack trace (attached). Thanks for insights, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B Script started on 2018-11-01 16:16:02+01:00 root@hermes:~/tmp

Re: backend crash on DELETE, reproducible locally

On Thu, Nov 01, 2018 at 03:42:57PM +0100, Karsten Hilbert wrote: > Will attempt to get a stack trace ... Meanwhile, in case it helps, an strace of the crash. epoll_wait(7, [{EPOLLIN, {u32=47607120, u64=35184419695952}}], 1, -1) = 1 recv(13, "Q\0\0\0`DELETE FROM ref.auto_hint W".

Re: backend crash on DELETE, reproducible locally

> I am running this delete statement via psql > > DELETE FROM ref.auto_hint WHERE title = 'Kontraindikation: ACE/Sartan > <-> Schwangerschaft'; > > and it crashes the backend it is running on. For what it is worth I have identified the physical file gnumed_v21=# select pg_relati

Re: Broken postgres links need to find callers [FIXED]

On Wed, 31 Oct 2018, Rich Shepard wrote: I managed to mess up postgresql-10.3 on this Slackware-14.2 desktop server/workstation. It worked OK until I tried adding access to an another application. The problems have been resolved by upgrading 10.3 to 10.5 using the SlackBuild.org script. Ther

Re: backend crash on DELETE, reproducible locally

Karsten Hilbert writes: > I am running this delete statement via psql > DELETE FROM ref.auto_hint WHERE title = 'Kontraindikation: ACE/Sartan > <-> Schwangerschaft'; Doesn't look particularly special ... > What else can I provide/test/look into ? Stack trace would be helpful, self-contai

backend crash on DELETE, reproducible locally

Dear list members ! I am running this delete statement via psql DELETE FROM ref.auto_hint WHERE title = 'Kontraindikation: ACE/Sartan <-> Schwangerschaft'; against a name| server_version setting | 11.0 (Debian 11.0-1) postgres@hermes:~$ psql

Re: incomplete startup packet messages in logs

Thanks Steve, It was indeed monitoring. i just had one check enabled, except the others which was not a check that queried postgres but a simple tcp port available. check-ports.rb -H localhost -p 5432 which was basically just creating a socket to check if it is is listening but not closing it.

Re: incomplete startup packet messages in logs

i have disabled all monitoring i am aware of for postgresql. ( it is sensu/collectd/ and occasional queries via haproxy and pgbouncer) i have disabled them all. i did tcpdump (i am not a pro here), but ignored the ip6 loopback. i'll paste the output once i get again. Regards, Vijay On Thu, Nov

Re: incomplete startup packet messages in logs

ok i enabled log_connections 2018-11-01 13:44:18 UTC LOG: connection received: host=::1 port=47574 2018-11-01 13:44:18 UTC LOG: incomplete startup packet i see this in my /etc/hosts ::1 localhost ip6-localhost ip6-loopback and host all all ::1/128 md5 in my hba,conf i'll ch

Re: incomplete startup packet messages in logs

> On Nov 1, 2018, at 1:27 PM, Vijaykumar Jain wrote: > > Hi Team, > > I know this is has been answered a lot on the internet wrt ignoring, > but i am unable to figure out why I get these messages in logs > "incomplete startup packet" Check which of your monitoring systems is configured to ch

incomplete startup packet messages in logs

Hi Team, I know this is has been answered a lot on the internet wrt ignoring, but i am unable to figure out why I get these messages in logs "incomplete startup packet" I have a server running postgresql 10.5 on ubuntu16.04 i do not have any active external connections other than streaming postg