Re: [GENERAL] How does one make the following psql statement sql-injection resilient?

On Monday, March 16, 2015, Alvaro Herrera wrote: > David G. Johnston wrote: > > > Thanks! I got the gist even with the typo. I actually pondered about > > prepare/execute after hitting send. Am I correct in remembering that > > "CREATE TEMP TABLE" cannot be prepared? I was using the actual qu

Re: [GENERAL] How does one make the following psql statement sql-injection resilient?

David G. Johnston wrote: > Thanks! I got the gist even with the typo. I actually pondered about > prepare/execute after hitting send. Am I correct in remembering that > "CREATE TEMP TABLE" cannot be prepared? I was using the actual query with > CREATE TEMP TABLE and then issuing "\copy" to dum

Re: [GENERAL] Postgresql BDR(Bi-Directional Replication) Latency Monitoring

Hi I just replied to a fairly similar question here: http://www.postgresql.org/message-id/CAMsr+YEqeFXDS=amqbzmopqdfnt+ho50uv-+s854kw9oa-x...@mail.gmail.com and I'll follow up on that thread as it's essentially the same thing. On 17 March 2015 at 02:27, Bill Brown wrote: > Hi All, > > I'm loo

Re: [GENERAL] bdr replication latency monitoring

Steve, The relevant change was made during the commit of logical decoding to PostgreSQL 9.4, where the field of interest was renamed from 'xmin' to 'catalog_xmin'. It's around then that pg_stat_logical_decoding was renamed to pg_replication_slots too. To get lag in bytes, use: SELECT slot_name,

Re: [GENERAL] pitr archive_command cp fsync

On 3/14/15 3:27 PM, Миша Тюрин wrote: > should we add disclaimer in pitr documentation about cp and fsync? > > cp does not fsync. > > and dd for example can do fsync. only on some platforms -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscript

Re: [GENERAL] Group by range in hour of day

On Mon, Mar 16, 2015 at 4:16 PM, Israel Brewster wrote: > On Mar 16, 2015, at 2:22 PM, David G. Johnston > wrote: > > > On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver > wrote: > >> On 03/16/2015 02:57 PM, Israel Brewster wrote: >> >>> I have a table with two timestamp columns for the start time

Re: [GENERAL] Group by range in hour of day

On 03/16/2015 04:16 PM, Israel Brewster wrote: On Mar 16, 2015, at 2:22 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: On 03/16/2015 02:57 PM, Israel Brewster wrote: I have a

Re: [GENERAL] Group by range in hour of day

On Mar 16, 2015, at 2:22 PM, David G. Johnston wrote: > > On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver > wrote: > On 03/16/2015 02:57 PM, Israel Brewster wrote: > I have a table with two timestamp columns for the start time and end > time of each record (call

Re: [GENERAL] Group by range in hour of day

Assuming 3 things Table name - test Column names - start_time, end_time Added an id column (int) to distinguish each record in the table You can go with this. (my apologies for formatting issues) with slots as ( select * fromgenerate_series(0,1439) as s(slot) ), slots_hours as (

Re: [GENERAL] Group by range in hour of day

On Mar 16, 2015, at 2:16 PM, Paul Jungwirth wrote: > >> I have a table with two timestamp columns for the start time and end >> time of each record (call them start and end).I'm trying to figure out >> if there is a way to group these records by "hour of day", > > I think you can do this by sel

Re: [GENERAL] Group by range in hour of day

On Mar 16, 2015, at 2:13 PM, Adrian Klaver wrote: > > On 03/16/2015 02:57 PM, Israel Brewster wrote: >> I have a table with two timestamp columns for the start time and end >> time of each record (call them start and end).I'm trying to figure out >> if there is a way to group these records by "ho

Re: [GENERAL] Group by range in hour of day

On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver wrote: > On 03/16/2015 02:57 PM, Israel Brewster wrote: > >> I have a table with two timestamp columns for the start time and end >> time of each record (call them start and end).I'm trying to figure out >> if there is a way to group these records by

Re: [GENERAL] Group by range in hour of day

I have a table with two timestamp columns for the start time and end time of each record (call them start and end).I'm trying to figure out if there is a way to group these records by "hour of day", I think you can do this by selecting `FROM generate_series(0, 23) s(h)` and then joining to your

Re: [GENERAL] Group by range in hour of day

On 03/16/2015 02:57 PM, Israel Brewster wrote: I have a table with two timestamp columns for the start time and end time of each record (call them start and end).I'm trying to figure out if there is a way to group these records by "hour of day", that is the record should be included in the group

Re: [GENERAL] How does one make the following psql statement sql-injection resilient?

On Mon, Mar 16, 2015 at 2:51 PM, Andy Colson wrote: > On 3/16/2015 4:45 PM, Andy Colson wrote: > >> On 3/16/2015 4:30 PM, David G. Johnston wrote: >> >>> psql "$SERVICE" \ >>> --echo-queries \ >>> --set=string_input="${1:-ok_to_return}" \ >>> --set=start="${2:-5}" \ >>> --

[GENERAL] Group by range in hour of day

I have a table with two timestamp columns for the start time and end time of each record (call them start and end).I'm trying to figure out if there is a way to group these records by "hour of day", that is the record should be included in the group if the hour of the day for the group falls anywhe

Re: [GENERAL] How does one make the following psql statement sql-injection resilient?

On 3/16/2015 4:45 PM, Andy Colson wrote: On 3/16/2015 4:30 PM, David G. Johnston wrote: psql "$SERVICE" \ --echo-queries \ --set=string_input="${1:-ok_to_return}" \ --set=start="${2:-5}" \ --set=end="${3:-10}" \ <<'SQL' SELECT idx FROM generate_series(1, 20)

Re: [GENERAL] How does one make the following psql statement sql-injection resilient?

On 3/16/2015 4:30 PM, David G. Johnston wrote: psql "$SERVICE" \ --echo-queries \ --set=string_input="${1:-ok_to_return}" \ --set=start="${2:-5}" \ --set=end="${3:-10}" \ <<'SQL' SELECT idx FROM generate_series(1, 20) gs (idx) WHERE 'short-circuit' !

[GENERAL] How does one make the following psql statement sql-injection resilient?

psql "$SERVICE" \ --echo-queries \ --set=string_input="${1:-ok_to_return}" \ --set=start="${2:-5}" \ --set=end="${3:-10}" \ <<'SQL' SELECT idx FROM generate_series(1, 20) gs (idx) WHERE 'short-circuit' != :'string_input' AND idx BETWEEN :start AND :en

Re: [GENERAL] Slow query with join

Tomas Vondra writes: > On 16.3.2015 19:50, Marc Watson wrote: >> I hope someone can help me with a problem I'm having when joining a >> view with a table. The view is somewhat involved, but I can provide the >> details if necessary > First, get rid of the ORDER BY clauses in the subselects - it's

Re: [GENERAL] Slow query with join

On 16.3.2015 19:50, Marc Watson wrote: > Hello all, > I am using PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit, as downloaded from EnterpriseDB, and is running on my dev system under Win 7 64-bit. > I hope someone can help me with a problem I'm having when joining a view with a table.

Re: [GENERAL] Slow query with join

On Mon, Mar 16, 2015 at 11:50 AM, Marc Watson wrote: > Hello all, > I am using PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit, as > downloaded from EnterpriseDB, and is running on my dev system under Win 7 > 64-bit. > ​[...]​ > > However, when I combine the two queries into one, th

[GENERAL] Slow query with join

Hello all, I am using PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit, as downloaded from EnterpriseDB, and is running on my dev system under Win 7 64-bit. I hope someone can help me with a problem I'm having when joining a view with a table. The view is somewhat involved, but I can

[GENERAL] Postgresql BDR(Bi-Directional Replication) Latency Monitoring

Hi All, I'm looking at BDR monitoring documentation at this location https://wiki.postgresql.org/wiki/BDR_Monitoring I understand the query in documentation: select slot_name, plugin, database, active, xmin, pg_get_transaction_committime(xmin) FROM pg_stat_logical_decodi

Re: [GENERAL] psql sqlstate return code access

On Mon, Mar 16, 2015 at 4:23 PM, Little, Doug C wrote: > insert into x select … from y; > > > > insert into log(message, code,timestamp) values('insert into > x',:SQLSTATE,now()); > > I'm pretty sure you have to wrap it into a plpgsql function: http://www.postgresql.org/docs/current/static/plpgs

Re: [GENERAL] Update using non-existent fields does not throw an error

Thanks very much. Now that you've explained it, it should have been obvious. RobR -Original Message- From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Sent: Monday, March 16, 2015 12:21 PM To: Rob Richardson; pgsql-general@postgresql.org Subject: RE: Update using non-existent fields d

Re: [GENERAL] Update using non-existent fields does not throw an error

Rob Richardson wrote: > An update query is apparently succeeding, even though the query refers to > fields that do not exist. > Here’s the query: > > update inventory set > x_coordinate = (select x_coordinate from bases where base = '101'), > y_coordinate = (select y_coordinate from bases where b

Re: [GENERAL] Update using non-existent fields does not throw an error

On 16 March 2015 at 17:02, Rob Richardson wrote: > Greetings! > > > > An update query is apparently succeeding, even though the query refers to > fields that do not exist. Here’s the query: > > > > update inventory set > > x_coordinate = (select x_coordinate from bases where base = '101'), > > y_

[GENERAL] Update using non-existent fields does not throw an error

Greetings! An update query is apparently succeeding, even though the query refers to fields that do not exist. Here's the query: update inventory set x_coordinate = (select x_coordinate from bases where base = '101'), y_coordinate = (select y_coordinate from bases where base = '101') where char

[GENERAL] psql sqlstate return code access

Hi, I have a requirement to log into a table the completion code for each statement being submitted in a psql script. I've looked around and can't seem to find how to access the sqlstate completion code. Is there a system variable in psql available or some other trick to get it? For exam

[GENERAL] how to recovery your database when stops replicating

Hi all, I´m testing BDR and have a big deal. When it gets corrupted, after time of working well, I cannot do a recovery and the only way to get the system working again seems to reset all again. It means: - Stop all nodes. - Delete data directories in both nodes -