Re: SQL syntax

> On Mar 2, 2018, at 5:41 PM, Adrian Klaver wrote: > >> On 03/02/2018 04:36 PM, Dale Seaburg wrote: >> My mind is drawing a blank. Not sure where to go to find the answer. Here >> is the statement in C#: >> sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE "; >> To finish off the W

Re: SQL syntax

Adrian Klaver writes: > On 03/02/2018 04:36 PM, Dale Seaburg wrote: >> To finish off the WHERE clause, I need to look at the first 2 letters, >> like "D:".  My question is how do I specify in the WHERE clause, to look >> at the first 2 characters in the Image_Filename column?  What is the >> co

Re: SQL syntax

On Fri, Mar 2, 2018 at 4:41 PM, Adrian Klaver wrote: > On 03/02/2018 04:36 PM, Dale Seaburg wrote: > >> My mind is drawing a blank. Not sure where to go to find the answer. >> Here is the statement in C#: >> >> sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE "; >> >> To finish off t

Re: SQL syntax

On 03/02/2018 04:36 PM, Dale Seaburg wrote: My mind is drawing a blank.  Not sure where to go to find the answer. Here is the statement in C#: sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE "; To finish off the WHERE clause, I need to look at the first 2 letters, like "D:".  My

SQL syntax

My mind is drawing a blank.  Not sure where to go to find the answer.  Here is the statement in C#: sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE "; To finish off the WHERE clause, I need to look at the first 2 letters, like "D:".  My question is how do I specify in the WHERE cl

Re: Is there a continuous backup for pg ?

Vick, I would love to use ZFS, this project requires RHEL/SeLinux MLS. Without MLS and RH support, ZFS is a no-go. On Fri, Mar 2, 2018 at 2:34 PM, Vick Khera wrote: > On Fri, Mar 2, 2018 at 2:34 PM, Gary M wrote: > >> Thanks Josh, >> >> PITR is the option I was recommending. Project management

Re: Jira database won't start after disk filled up

Yes, contradictory. We recently disabled email forwarding, so on 1/10 when the disk filled up, we never received any alerts. New position, so I was completely unaware of this database, except on a conceptual level, until Tuesday. I think the best I can do is get this database back to 1/10. In m

Re: Jira database won't start after disk filled up

On Fri, Mar 2, 2018 at 4:32 PM, Paul Costello wrote: > I have a database that wouldn't start due to the disk filling up back on > 1/10, unbeknownst to us until 2/27. This is jira, so it's critical data. > It appears jira was running in memory that entire time. > Those first two sentences seem

Re: Is there a continuous backup for pg ?

On Fri, Mar 2, 2018 at 2:34 PM, Gary M wrote: > Thanks Josh, > > PITR is the option I was recommending. Project management diligence > dictates I need at least one other option from a different recommending > source, two other options optimally. > File system snapshot on a robust system like ZFS

Jira database won't start after disk filled up

I have a database that wouldn't start due to the disk filling up back on 1/10, unbeknownst to us until 2/27. This is jira, so it's critical data. It appears jira was running in memory that entire time. I needed to run pg_resetxlog -f in order to start the database. It started, but upon logging i

Re: Is there a continuous backup for pg ?

Thanks everyone !! I think I have enough alternatives for this project. I'll start testing on Monday.. have a good weekend -g On Fri, Mar 2, 2018 at 12:55 PM, David Steele wrote: > Hi Gary, > > On 3/2/18 2:05 PM, Gary M wrote: > > Hi, > > > > I have an unusual requirement for schema based, liv

Re: PQConsumeinput stuck on recv

Hi, On 2018-03-01 19:20:04 -0300, Andre Oliveira Freitas wrote: > I was able to capture the backtrace again, now with libpq debugging symbols. > > Thread 15 (Thread 0x7f8cec068700 (LWP 68)): > #0 0x7f8d252a1d9b in __libc_recv (fd=150, buf=0x7f8cf0034410, > n=16384, flags=623517083, flags@en

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On 03/02/2018 10:58 AM, Alexander Farber wrote: I see, thank you for your comments, David and Adrian. In the "tiles" column actually save either the JSON array of tiles - when the user plays them Or a string (which is jsonb too) concatenated of letters - when the user swaps the letters. Ma

Re: Is there a continuous backup for pg ?

Hi Gary, On 3/2/18 2:05 PM, Gary M wrote: > Hi, > > I have an unusual requirement for schema based, live backup of a 24/7 > database processing 100K inserts/updates per hour. The data store is > around 100TB. > > The requirement is supporting an incremental backup of 10 minute > windows.  Replic

Re: Is there a continuous backup for pg ?

Thanks Josh, PITR is the option I was recommending. Project management diligence dictates I need at least one other option from a different recommending source, two other options optimally. On Fri, Mar 2, 2018 at 12:16 PM, Joshua D. Drake wrote: > On 03/02/2018 11:05 AM, Gary M wrote: > >> Hi,

Re: Is there a continuous backup for pg ?

> On Mar 2, 2018, at 11:05 AM, Gary M wrote: > > Hi, > > I have an unusual requirement for schema based, live backup of a 24/7 > database processing 100K inserts/updates per hour. The data store is around > 100TB. > > The requirement is supporting an incremental backup of 10 minute windows.

Re: Is there a continuous backup for pg ?

On 03/02/2018 11:05 AM, Gary M wrote: Hi, I have an unusual requirement for schema based, live backup of a 24/7 database processing 100K inserts/updates per hour. The data store is around 100TB. The requirement is supporting an incremental backup of 10 minute windows.  Replication is not co

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On Fri, Mar 2, 2018 at 11:58 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > I see, thank you for your comments, David and Adrian. > > In the "tiles" column actually save either the JSON array of tiles - when > the user plays them > > Or a string (which is jsonb too) concatenated of le

Is there a continuous backup for pg ?

Hi, I have an unusual requirement for schema based, live backup of a 24/7 database processing 100K inserts/updates per hour. The data store is around 100TB. The requirement is supporting an incremental backup of 10 minute windows. Replication is not considered backup from malicious action. Are t

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

I see, thank you for your comments, David and Adrian. In the "tiles" column actually save either the JSON array of tiles - when the user plays them Or a string (which is jsonb too) concatenated of letters - when the user swaps the letters. Maybe I should rethink my table structure (I just want t

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On 03/02/2018 10:43 AM, Alexander Farber wrote: # select * from words_moves where gid=609 limit 3; -[ RECORD 1 ]

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On Fri, Mar 2, 2018 at 11:48 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Oops, I've got strings there too - when swapping instead of playing tiles: > > ​You should probably add: jsonb_array_length(tiles) > 0 as a check constraint on column ​ David J.

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Oops, I've got strings there too - when swapping instead of playing tiles: # select * from words_moves where gid=609 and action <> 'play'; mid | action | gid | uid |played | tiles | score --++-+--+---+--+---

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

# select * from words_moves where gid=609 limit 3; -[ RECORD 1 ]---

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Good evening - On Fri, Mar 2, 2018 at 7:11 PM, Adrian Klaver wrote: > >> 2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms bind >> : SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS >> apns, out_adm AS adm, out_body AS body FROM >> words_play_game($1::int, $2

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On 03/02/2018 10:04 AM, David G. Johnston wrote: On Friday, March 2, 2018, Adrian Klaver > wrote: On 03/02/2018 06:42 AM, Alexander Farber wrote: 2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.058 ms  bind : SELECT out_uid  AS uid,  out_fcm

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On Friday, March 2, 2018, Adrian Klaver wrote: > On 03/02/2018 06:42 AM, Alexander Farber wrote: > >> >> > 2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms bind > : SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns, > out_adm AS adm, out_body AS body FROM words_play_game

Re: How can I include sql file in pgTAP unittest?

On 03/02/2018 06:46 AM, Stéphane Klein wrote: Sorry, it is not kea but db2 in my example. I did a mistake when I replace the db name in my example. You need to read:   CREATE EXTENSION IF NOT EXISTS postgres_fdw;   DROP SERVER IF EXISTS db2 CASCADE;   CREATE SERVER db2 FOREIGN DATA WRAP

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On 03/02/2018 06:42 AM, Alexander Farber wrote: Hi Adrian, I 100% agree that nobody except me should debug my huge stored function, but if you look at my PostgreSQL 10.3 log - Another thought, in : 2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms bind : SELECT out_uid AS uid,

Re: How to perform PITR when all of the logs won't fit on the drive

On Thu, Mar 1, 2018 at 2:28 PM, Tony Sullivan wrote: > Hello, > > I have a situation where something was deleted from a database that > shouldn't have been so I am having to take a base backup and perform a > point-in-time-recovery. The problem I have is that the decompressed WAL > files will not

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On 03/02/2018 06:42 AM, Alexander Farber wrote: Hi Adrian, I 100% agree that nobody except me should debug my huge stored function, but if you look at my PostgreSQL 10.3 log - Which proves what has already been proven, that at least some of the data is correct. The issue is data that is not

Re: Release upgarde failure

On Fri, Mar 2, 2018 at 7:55 AM, Pawan Sharma wrote: > Hi All, > > Please let me know the reasons of release upgrade failure in PostgreSQL > which you guys faced while doing it. > > Example: 9.5.1 to 9.5.2 > > ​It is highly unlikely that you will encounter a failure when upgrading to a new minor r

Release upgarde failure

Hi All, Please let me know the reasons of release upgrade failure in PostgreSQL which you guys faced while doing it. Example: 9.5.1 to 9.5.2

Re: Enforce primary key on every table during dev?

On 02/03/2018 01:10 , Daevor The Devoted wrote: On Fri, Mar 2, 2018 at 12:05 AM, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote: On 02/03/18 06:47, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar mailto:rakeshkumar...@aol.com>

Re: How can I include sql file in pgTAP unittest?

2018-03-02 14:52 GMT+01:00 Adrian Klaver : > On 03/02/2018 01:17 AM, Stéphane Klein wrote: > >> Hi, >> >> context: I would like to write UnitTest to test pgSQL triggers which use >> postgres_fdw extension. >> I use pgTAP to write this UnitTest (I use this >> Docker environment

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Hi Adrian, I 100% agree that nobody except me should debug my huge stored function, but if you look at my PostgreSQL 10.3 log - On Fri, Mar 2, 2018 at 3:31 PM, Adrian Klaver wrote: > > The little gray cells are not awake enough to work through the below:) If > it where me I would first confirm t

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On 03/02/2018 06:14 AM, Alexander Farber wrote: Hi Adrian, thank you for the reply - #  select mid, jsonb_array_length(tiles) from words_moves where gid=609; ERROR:  22023: cannot get array length of a scalar LOCATION:  jsonb_array_length, jsonfuncs.c:1579

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

And here is how I call my stored function - https://gist.github.com/afarber/88a832a1b90a8940764ad69b2b761914 - why wouldn't it store a jsonb array in the tiles column of words_moves table? 2018-03-02 15:29:42.644 CET [16693] LOG: statement: DISCARD ALL 2018-03-02 15:29:42.644 CET [16693] LOG:

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Hi Adrian, thank you for the reply - On Fri, Mar 2, 2018 at 3:05 PM, Adrian Klaver wrote: > On 03/02/2018 05:52 AM, Alexander Farber wrote: > >> >> in PostgreSQL 10.3 I have the following table with a jsonb column: >> >> # \d words_moves; >> Table "public.w

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

On 03/02/2018 05:52 AM, Alexander Farber wrote: Good afternoon, in PostgreSQL 10.3 I have the following table with a jsonb column: #  \d words_moves;                                      Table "public.words_moves"  Column |           Type           | Collation | Nullable |     D

jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Good afternoon, in PostgreSQL 10.3 I have the following table with a jsonb column: # \d words_moves; Table "public.words_moves" Column | Type | Collation | Nullable | Default +--+---+--+

Re: How can I include sql file in pgTAP unittest?

On 03/02/2018 01:17 AM, Stéphane Klein wrote: Hi, context: I would like to write UnitTest to test pgSQL triggers which use postgres_fdw extension. I use pgTAP to write this UnitTest (I use this Docker environment poc-postgresql-pgTAP

Tracking changes DML in history log table

Hello, i want to track changes (DML) within one table like here: https://www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/ I need script or tool which will be generating SQL script for each row with possibility to rollback each statement, please help Guys, Best wishes, Jacek

How can I include sql file in pgTAP unittest?

Hi, context: I would like to write UnitTest to test pgSQL triggers which use postgres_fdw extension. I use pgTAP to write this UnitTest (I use this Docker environment poc-postgresql-pgTAP ). All works perfectly with this test fi

Re: PG 10 logical replication version dependency?

Colin 't Hart schrieb am 02.03.2018 um 09:23: > Is there a version dependency when using logical replication? Do both > sides have to be running the same major version? Or is it a wire > protocol that will be backwards compatible in future releases? > > I sincerely hope it's the latter so that use

PG 10 logical replication version dependency?

Hi, Is there a version dependency when using logical replication? Do both sides have to be running the same major version? Or is it a wire protocol that will be backwards compatible in future releases? I sincerely hope it's the latter so that users of this aren't forced to upgrade all of their in