Re: [GENERAL] Fwd: Query parameter types not recognized

[David G. Johnston]

In short - this is the wrong list (pgsql-j...@postgresql.org is the
appropriate one; or the official GitHub repo) and you need to provide some
working self-contained examples showing exactly what you are doing.​​

On Fri, Feb 10, 2017 at 8:17 AM, Roberto Balarezo 
wrote:

> Hi, I would like to know why this is happening and some advice if there is
> a way to solve this problem:
>
> I have a query like this:
>
> select COALESCE(duedate, ? + 1) from invoices order by duedate desc limit 10;
>
> ​If anything is wrong here it is a JDBC bug - not PostgreSQL core - and
should be reported there.  If you write the above using:

PREPARE testquery AS ... - i.e., no type specification - ​you get the same
error - which happens because PostgreSQL guesses and assumes the left
operand of the"+(?,integer)" operator is going to be integer (how exactly
it comes to that conclusion I do not know).

> ​[...]
>
>


> ​
> However, when I try to execute the query, I get this error:
>
org.postgresql.util.PSQLException: ERROR: COALESCE types timestamp
without time zone and integer cannot be matched
>
> ​So JDBC must not telling the server about the expected type of parameter
#1 when it sends the prepare command and statement text, otherwise the
server wouldn't complain.  If you are using "setDate" that seems unlikely -
but I'm not familiar enough with the JDBC implementation to know for sure.


> Why is it inferring that the type is integer, when I send it as Date??
>
> When I force the type using a cast, like this:
>
> select COALESCE(duedate, CAST(? AS DATE) + 1) from invoices order by duedate 
> desc limit 10;
>
> I get this error:
>
> org.postgresql.util.PSQLException: ERROR: could not determine data type of 
> parameter $1
>
> ​This form can be prepared successfully directly in SQL even without an
explicit specification of the data type in the PREPARE SQL command - so I'm
led to believe, due to the lack of a self-contained test case - that you
are possibly doing something wrong in the actual code - hence my second
piece of advice at the top.

​David J.

[GENERAL] BDR problem rejoining node

[Tanner Kerr]

I have two databases being replicated across three nodes with bdr. The
third node filled up and crashed. I removed this node from the group
successfully, but now I'm having trouble rejoining it. I'm able to re-join
the one database no problem. However, trying to do a bdr join on the other
causes the two active nodes to slow down considerably, and refuses to join.
This database copies the data after a few minutes, however, the join fails
and bdr.bdr_nodes on the third node shows only itself in the group with
status 'i'. The active nodes don't show anything different in the
bdr.bdr_nodes. All they can see is a new pg_replication_slot for that node.
The logs say "previous init failed, manual cleanup is required" Even though
there are no bdr_connections or broken bdr_nodes entries. Everytime I've
tried, I've removed the pg_replication slots, the bdr_connections, and the
bdr_nodes (bdr.remove_bdr_from_local_node()). I've also dropped the cluster
and recreated it again each time with:

pg_dropcluster 9.4 main --stop
/usr/bin/pg_createcluster --start 9.4 -d /var/lib/postgresql/9.4/main main

The database failing to copy is small only around 60M. The one being copied
successfully, is around 1600M.

The join command I've been using:

select bdr.bdr_group_join(local_node_name := 'NODENAMEOFREBUILTNODE',
node_external_dsn := 'host=IPOFREBUILTNODE port=5432 dbname=MYDB
connect_timeout=50 keepalives_idle=5 keepalives_interval=1', join_using_dsn
:= 'host=ACTIVENODEIP  port=5432 dbname=MYDB connect_timeout=50
keepalives_idle=5 keepalives_interval=1');

I'm not sure why it works for one database and not the other.

The log from the failed join attemp is below:

2017-02-10 14:49:24 PST [6981-10] LOG:  Creating replica with:
/usr/lib/postgresql/9.4/bin/bdr_initial_load --snapshot 00C58720-1 --source
"host=ACTIVEIP  port=5432 dbname=MYDB connect_timeout=5
0 keepalives_idle=5 keepalives_interval=1" --target "host=REBUILDHOST
port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5
keepalives_interval=1" --tmp-directory "/tmp/postgres-bdr-00C58720-1
.6981", --pg-dump-path "/usr/lib/postgresql/9.4/bin/bdr_dump",
--pg-restore-path "/usr/lib/postgresql/9.4/bin/pg_restore"
Dumping remote database "connect_timeout=30 keepalives=1 keepalives_idle=20
keepalives_interval=20 keepalives_count=5   host=ACTIVEIP  port=5432
dbname=MYDB connect_timeout=50 keepalives_idle=5
keepalives_interval=1 fallback_application_name='bdr
(6385577151748866557,1,16391,): init_replica dump'" with 1 concurrent
workers to "/tmp/postgres-bdr-00C58720-1.6981"
Restoring dump to local DB "host=REBUILDHOST port=5432 dbname=MYDB
connect_timeout=50 keepalives_idle=5 keepalives_interval=1
fallback_application_name='bdr (6385577151748866557,1,16391,): init_replica
restore' options='-c bdr.do_not_replicate=on  -c
bdr.permit_unsafe_ddl_commands=on -c bdr.skip_ddl_replication=on -c
bdr.skip_ddl_locking=on -c session_replication_role=replica'" with 1
concurrent workers from "/tmp/postgres-bdr-00C58720-1.6981"
2017-02-10 14:57:16 PST [7271-1] postgres@MYDB NOTICE:  extension
"btree_gist" already exists, skipping
2017-02-10 14:57:16 PST [7271-2] postgres@MYDB NOTICE:  extension "bdr"
already exists, skipping
2017-02-10 14:57:16 PST [7271-3] postgres@MYDB NOTICE:  extension "plpgsql"
already exists, skipping
2017-02-10 14:57:16 PST [7271-4] postgres@MYDB NOTICE:  extension
"pgcrypto" already exists, skipping
2017-02-10 14:57:16 PST [7271-5] postgres@MYDB NOTICE:  extension
"uuid-ossp" already exists, skipping

*alters tables and builds indexes*

2017-02-10 14:57:23 PST [6981-10] DEBUG:  syncing bdr_nodes and
bdr_connections
2017-02-10 14:57:23 PST [6981-11] ERROR:  BEGIN or table locking on remote
failed:
2017-02-10 14:57:23 PST [6884-11] LOG:  worker process: bdr db: MYDB (PID
6981) exited with exit code 1
2017-02-10 14:57:28 PST [6884-12] LOG:  starting background worker process
"bdr db: MYDB"
2017-02-10 14:57:28 PST [7274-1] NOTICE:  version "1.0" of extension
"btree_gist" is already installed
2017-02-10 14:57:28 PST [7274-2] NOTICE:  version "1.0.1.0" of extension
"bdr" is already installed
2017-02-10 14:57:28 PST [7274-3] DEBUG:  per-db worker for node bdr
(6385577151748866557,1,16391,) starting
2017-02-10 14:57:28 PST [7274-4] DEBUG:  init_replica init from remote
host=ACTIVEIP  port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5
keepalives_interval=1
2017-02-10 14:57:28 PST [7274-5] ERROR:  previous init failed, manual
cleanup is required
2017-02-10 14:57:28 PST [7274-6] DETAIL:  Found bdr.bdr_nodes entry for bdr
(6385577151748866557,1,16391,) with state=i in remote bdr.bdr_nodes
2017-02-10 14:57:28 PST [7274-7] HINT:  Remove all replication identifiers
and slots corresponding to this node from the init target node then drop
and recreate this database and try again


Tanner

Re: [GENERAL] Fwd: Query parameter types not recognized

[rob stone]

Hello Roberto,
On Fri, 2017-02-10 at 16:43 -0500, Roberto Balarezo wrote:
> Hi Rob,
> 
> Thanks for your answer. The query is just an example I made to
> illustrate the problem. In the database I'm working with, duedate is
> a timestamp without timezone column, which can contain null values.
> The parameter is supposed to be of type DATE. From Java, I'm sending
> a Date object (which contains no timezone information, so the driver
> should not have problem with this). So if the field duedate has a
> null value, a default date with one day added is returned.
> I read that the driver has problems with timestamp columns, because
> it cannot tell the server if it is a timestamp with or without
> timezone, but dates should not present this problem. The server
> should know it is of DATE type.
> 
> PS: I have changed the code of the application to send the value
> (defaultDate + 1 day) calculated in the application and sent this as
> a parameter to make it work, but there are many queries like this and
> I would like to know why it happens and if I can make it work
> changing the query and not the code.
> 

If the column duedate is defined as a timestamp, then setDate is not
the answer. java.sql.Date is just a "date".
java.util.Date is a timestamp object but from 1.8 onwards it is pretty
well deprecated in favour of the Calendar methods. You can of course
have 0:0:0 as the time part.
All I can suggest is a spot of reading the docs about Calendar and
formatting your (defaultDate + 1) as a timestamp.


HTH,
Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: Query parameter types not recognized

[Adrian Klaver]

On 02/10/2017 02:14 PM, Roberto Balarezo wrote:

Hmmm... I didn't know PostgreSQL had a facility for query logging and
debugging of parameters to a logfile. Thought I had to execute a
describe or something like that. Thanks, I'll try it to see what's
happening!


Start here:

https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html

Set up logging and then set :

log_statement = 'all'

This will generate a lot of logs so you will probably not want to keep 
it that way.


A Python example:

In [6]: date.today()
Out[6]: datetime.date(2017, 2, 10)

In [7]: cur.execute('select %s', [date.today()])

From Postgres log;

aklaver-2017-02-10 14:35:42.842 PST-0LOG:  statement: BEGIN
aklaver-2017-02-10 14:35:42.842 PST-0LOG:  statement: select 
'2017-02-10'::date





2017-02-10 16:57 GMT-05:00 Adrian Klaver >:

On 02/10/2017 01:51 PM, Roberto Balarezo wrote:

Hi,

The parameter defaultDueDate is a java.sql.Date object, an
actual Date.
When I run the query with the value in it, it works:
```sql
db=> select COALESCE(duedate, date '2017-02-01' + 1) from
invoices order
by duedate desc;
  coalesce
-
 2017-02-02 00:00:00
 2017-02-02 00:00:00
 2016-11-14 00:00:00
 2017-02-10 00:00:00
 2017-02-02 00:00:00
 2017-02-13 00:00:00
 2017-02-02 00:00:00
 2017-02-02 00:00:00
```

But when I send it as a parameter, it ignores it and seems to
think the
expression is of type interger.


Which would indicate to me that is what is being passed in the
parameter. If I would guess, from information here:

https://docs.oracle.com/javase/7/docs/api/java/sql/Date.html


milliseconds since January 1, 1970 00:00:00.000 GMT.

Turn on/up logging in Postgres and run a query with that
java.sql.Date object. I am betting that what you will see in the
logs is an integer.



2017-02-10 16:32 GMT-05:00 Adrian Klaver

>>:


On 02/10/2017 07:17 AM, Roberto Balarezo wrote:

Hi, I would like to know why this is happening and some
advice
if there
is a way to solve this problem:

I have a query like this:

|select COALESCE(duedate, ? + 1) from invoices order by
duedate desc
limit 10; |


What is the 1 in ? + 1 supposed to represent?


where ? is a query parameter. I’m using JDBC to connect
to the
database,
and sending parameters like this:

|query.setDate(1, defaultDueDate); |

Where defaultDueDate is a java.sql.Date object. However,
when I
try to
execute the query, I get this error:

|org.postgresql.util.PSQLException: ERROR: COALESCE
types timestamp
without time zone and integer cannot be matched |


So what is the actual value of defaultDueDate?

Looks like it is an integer from the ERROR message.

Might want to look in the Postgres logs to see if they show
anything
that might help.


Why is it inferring that the type is integer, when I
send it as
Date??


I don't use Java, but I did find the below, don't know if it
helps?:


https://jdbc.postgresql.org/documentation/94/escapes-datetime.html


>



When I force the type using a cast, like this:

|select COALESCE(duedate, CAST(? AS DATE) + 1) from invoices
order by
duedate desc limit 10; |

I get this error:

|org.postgresql.util.PSQLException: ERROR: could not
determine
data type
of parameter $1 |

If I’m telling PostgreSQL that the parameter is going to
be a
Date, and
send through the driver a Date, why it is having trouble
determining the
datatype of the parameter??
What can I do to make it work?

For reference, I’m using PostgreSQL 9.2.15 and JDBC driver
9.4.1207.jre6.

Thanks for your advice!

​



--
Adrian 

Re: [GENERAL] clarification about standby promotion

[Michael Paquier]

On Fri, Feb 10, 2017 at 7:15 PM, Jehan-Guillaume de Rorthais
 wrote:
> On Thu, 9 Feb 2017 18:27:30 +
> Rakesh Kumar  wrote:
>
>> >Sure, but when you are doing a switchover, the standby is supposed to be
>> >connected to the master when you shutdown the master. So based on the doc,
>> >the standby should receive **everything** from the master before the master
>> >actually shutdown.
>>
>> We use 9.5 and even in that version there is no handshake during role
>> reversal. In fact PG does not have concept of handshake and role reversal
>> unlike in Db2, oracle and sqlserver you can switchover from one to other by a
>> single command.
>>
>> Our DBAs use home grown script for switchover which does the following:
>>
>> 1 - first kill postmaster in the outgoing primary.
>
> Kill ? You mean "pg_ctl stop -m fast" right ?

If at shutdown the previous primary does not issue a checkpoint, there
is no way to be sure that it will begin replaying WAL from the point
after WAL has forked. In short, if you kill it, then try to connect it
back to the new promoted primary, it may be able to begin replicating
changes. And if at the moment it was killed an unfinished checkpoint
was running, you will much likely corrupt a couple of pages on your
primary.

>> 2 - promote the standby as the new primary
>> 3 - use timeline to resync former primary (of step 1) with the new primary
>> (step 2).
>
> Use timeline to resync ? Timeline is an internal mechanism in PostgreSQL, not
> a tool, so I don't get this step...You mean using pg_rewind ?
>
> So far, I stick to my procedure (given in another answer) which looks a lot
> more safer.

Definitely yes, Guillaune is right here. You need to rewind things.
After being sure that the previous primary has been stopped cleanly.
You could as well kill it once if you want to make your server go down
as fast as possible once. But at next startup let it recover
completely, then stop it cleanly, and finally you will be able to
recycle it consistently with pg_rewind.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: Query parameter types not recognized

[Adrian Klaver]

On 02/10/2017 01:51 PM, Roberto Balarezo wrote:

Hi,

The parameter defaultDueDate is a java.sql.Date object, an actual Date.
When I run the query with the value in it, it works:
```sql
db=> select COALESCE(duedate, date '2017-02-01' + 1) from invoices order
by duedate desc;
  coalesce
-
 2017-02-02 00:00:00
 2017-02-02 00:00:00
 2016-11-14 00:00:00
 2017-02-10 00:00:00
 2017-02-02 00:00:00
 2017-02-13 00:00:00
 2017-02-02 00:00:00
 2017-02-02 00:00:00
```

But when I send it as a parameter, it ignores it and seems to think the
expression is of type interger.


Which would indicate to me that is what is being passed in the 
parameter. If I would guess, from information here:


https://docs.oracle.com/javase/7/docs/api/java/sql/Date.html

milliseconds since January 1, 1970 00:00:00.000 GMT.

Turn on/up logging in Postgres and run a query with that java.sql.Date 
object. I am betting that what you will see in the logs is an integer.





2017-02-10 16:32 GMT-05:00 Adrian Klaver >:

On 02/10/2017 07:17 AM, Roberto Balarezo wrote:

Hi, I would like to know why this is happening and some advice
if there
is a way to solve this problem:

I have a query like this:

|select COALESCE(duedate, ? + 1) from invoices order by duedate desc
limit 10; |


What is the 1 in ? + 1 supposed to represent?


where ? is a query parameter. I’m using JDBC to connect to the
database,
and sending parameters like this:

|query.setDate(1, defaultDueDate); |

Where defaultDueDate is a java.sql.Date object. However, when I
try to
execute the query, I get this error:

|org.postgresql.util.PSQLException: ERROR: COALESCE types timestamp
without time zone and integer cannot be matched |


So what is the actual value of defaultDueDate?

Looks like it is an integer from the ERROR message.

Might want to look in the Postgres logs to see if they show anything
that might help.


Why is it inferring that the type is integer, when I send it as
Date??


I don't use Java, but I did find the below, don't know if it helps?:

https://jdbc.postgresql.org/documentation/94/escapes-datetime.html




When I force the type using a cast, like this:

|select COALESCE(duedate, CAST(? AS DATE) + 1) from invoices
order by
duedate desc limit 10; |

I get this error:

|org.postgresql.util.PSQLException: ERROR: could not determine
data type
of parameter $1 |

If I’m telling PostgreSQL that the parameter is going to be a
Date, and
send through the driver a Date, why it is having trouble
determining the
datatype of the parameter??
What can I do to make it work?

For reference, I’m using PostgreSQL 9.2.15 and JDBC driver
9.4.1207.jre6.

Thanks for your advice!

​



--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: Query parameter types not recognized

[Adrian Klaver]

On 02/10/2017 01:33 PM, Arjen Nienhuis wrote:



On Feb 10, 2017 8:11 PM, "Roberto Balarezo" > wrote:

Hi, I would like to know why this is happening and some advice if
there is a way to solve this problem:

I have a query like this:

|select COALESCE(duedate, ? + 1) from invoices order by duedate desc
limit 10; |

where ? is a query parameter. I’m using JDBC to connect to the
database, and sending parameters like this:

|query.setDate(1, defaultDueDate); |

If you want to add to a date you cannot just add 1. You need an
interval: coalesce(duedate, ? + interval '1 day')

See:

https://www.postgresql.org/docs/9.6/static/functions-datetime.html


Actually that is not the case, from above docs:

"Also, the + and * operators come in commutative pairs (for example both 
date + integer and integer + date); we show only one of each such pair."


and:

test=# select current_date;
date

 2017-02-10
(1 row)

test=# select current_date + 1;
  ?column?

 2017-02-11
(1 row)




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: Query parameter types not recognized

[Arjen Nienhuis]

On Feb 10, 2017 8:11 PM, "Roberto Balarezo"  wrote:

Hi, I would like to know why this is happening and some advice if there is
a way to solve this problem:

I have a query like this:

select COALESCE(duedate, ? + 1) from invoices order by duedate desc limit 10;

where ? is a query parameter. I’m using JDBC to connect to the database,
and sending parameters like this:

query.setDate(1, defaultDueDate);

If you want to add to a date you cannot just add 1. You need an interval:
coalesce(duedate, ? + interval '1 day')

See:

https://www.postgresql.org/docs/9.6/static/functions-datetime.html

Re: [GENERAL] Fwd: Query parameter types not recognized

[Adrian Klaver]

On 02/10/2017 07:17 AM, Roberto Balarezo wrote:

Hi, I would like to know why this is happening and some advice if there
is a way to solve this problem:

I have a query like this:

|select COALESCE(duedate, ? + 1) from invoices order by duedate desc
limit 10; |


What is the 1 in ? + 1 supposed to represent?



where ? is a query parameter. I’m using JDBC to connect to the database,
and sending parameters like this:

|query.setDate(1, defaultDueDate); |

Where defaultDueDate is a java.sql.Date object. However, when I try to
execute the query, I get this error:

|org.postgresql.util.PSQLException: ERROR: COALESCE types timestamp
without time zone and integer cannot be matched |


So what is the actual value of defaultDueDate?

Looks like it is an integer from the ERROR message.

Might want to look in the Postgres logs to see if they show anything 
that might help.




Why is it inferring that the type is integer, when I send it as Date??


I don't use Java, but I did find the below, don't know if it helps?:

https://jdbc.postgresql.org/documentation/94/escapes-datetime.html



When I force the type using a cast, like this:

|select COALESCE(duedate, CAST(? AS DATE) + 1) from invoices order by
duedate desc limit 10; |

I get this error:

|org.postgresql.util.PSQLException: ERROR: could not determine data type
of parameter $1 |

If I’m telling PostgreSQL that the parameter is going to be a Date, and
send through the driver a Date, why it is having trouble determining the
datatype of the parameter??
What can I do to make it work?

For reference, I’m using PostgreSQL 9.2.15 and JDBC driver 9.4.1207.jre6.

Thanks for your advice!

​




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Alter view with psql command line

[David Fetter]

On Fri, Feb 10, 2017 at 02:26:18PM -0300, Leonardo M. Ramé wrote:
> El 10/02/17 a las 14:17, Adrian Klaver escribió:
> > On 02/10/2017 09:09 AM, Leonardo M. Ramé wrote:
> > > Hi, is there a way to alter a view using *psql*?, something like what
> > > \ef does for functions.
> > 
> > In 9.6:
> > 
> 
> That's why in 9.1 I didn't find that command...

You can use the 9.6 client without problems on 9.1, well, apart from
the fact that 9.1 is already past its end of life.

Best,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: Query parameter types not recognized

[rob stone]

Hello Roberto,
On Fri, 2017-02-10 at 10:17 -0500, Roberto Balarezo wrote:
> Hi, I would like to know why this is happening and some advice if
> there is a way to solve this problem:
> 
> I have a query like this:
> 
> select COALESCE(duedate, ? + 1) from invoices order by duedate desc
> limit 10;
> where ? is a query parameter. I’m using JDBC to connect to the
> database, and sending parameters like this:
> 
> query.setDate(1, defaultDueDate);
> Where defaultDueDate is a java.sql.Date object. However, when I try
> to execute the query, I get this error:
> 
> org.postgresql.util.PSQLException: ERROR: COALESCE types timestamp
> without time zone and integer cannot be matched
> Why is it inferring that the type is integer, when I send it as
> Date??
> 
> 

Prepared statement's set.Date applies the current server timezone to
the value. So, if the database column duedate is of type DATE, it can't
interpret what you are trying to do.

If duedate can be null, then I really don't understand your query. 

HTH.

Rob





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Fwd: Query parameter types not recognized

[Roberto Balarezo]

Hi, I would like to know why this is happening and some advice if there is
a way to solve this problem:

I have a query like this:

select COALESCE(duedate, ? + 1) from invoices order by duedate desc limit 10;

where ? is a query parameter. I’m using JDBC to connect to the database,
and sending parameters like this:

query.setDate(1, defaultDueDate);

Where defaultDueDate is a java.sql.Date object. However, when I try to
execute the query, I get this error:

org.postgresql.util.PSQLException: ERROR: COALESCE types timestamp
without time zone and integer cannot be matched

Why is it inferring that the type is integer, when I send it as Date??

When I force the type using a cast, like this:

select COALESCE(duedate, CAST(? AS DATE) + 1) from invoices order by
duedate desc limit 10;

I get this error:

org.postgresql.util.PSQLException: ERROR: could not determine data
type of parameter $1

If I’m telling PostgreSQL that the parameter is going to be a Date, and
send through the driver a Date, why it is having trouble determining the
datatype of the parameter??
What can I do to make it work?

For reference, I’m using PostgreSQL 9.2.15 and JDBC driver 9.4.1207.jre6.

Thanks for your advice!
​

Re: [GENERAL] Alter view with psql command line

[Leonardo M . Ramé]

El 10/02/17 a las 14:17, Adrian Klaver escribió:

On 02/10/2017 09:09 AM, Leonardo M. Ramé wrote:

Hi, is there a way to alter a view using *psql*?, something like what
\ef does for functions.


In 9.6:



That's why in 9.1 I didn't find that command...


https://www.postgresql.org/docs/9.6/static/app-psql.html

\ev [ view_name [ line_number ] ]



Thanks Adrian.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Alter view with psql command line

[Adrian Klaver]

On 02/10/2017 09:09 AM, Leonardo M. Ramé wrote:

Hi, is there a way to alter a view using *psql*?, something like what
\ef does for functions.


In 9.6:

https://www.postgresql.org/docs/9.6/static/app-psql.html

\ev [ view_name [ line_number ] ]




Regards,



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Alter view with psql command line

[Leonardo M . Ramé]

Hi, is there a way to alter a view using *psql*?, something like what 
\ef does for functions.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Locks Postgres

[Jeff Janes]

On Thu, Feb 9, 2017 at 9:00 PM, Patrick B  wrote:

> Hi guys
>
> I just wanna understand the locks in a DB server:
> [image: Imagem inline 1]
>
> Access share = Does that mean queries were waiting because an
> update/delete/insert was happening?
>



It would seem more plausible that your chart is showing the locks that are
*held*, not the locks that are *waiting to be granted*.  But without
knowing where the chart came from, we can't know for sure.

If those are locks being held, it just means your server was kind of busy
(which you already knew).  But we don't know how busy.  A single complex
query can easily hold several dozens locks.

Cheers,

Jeff

Re: [GENERAL] Loose indexscan and partial indexes

[Peter J. Holzer]

On 2017-02-10 14:24:36 +0100, Thomas Kellerer wrote:
> Peter J. Holzer schrieb am 10.02.2017 um 14:02:
> > So it's doing a sequential scan on the initial select in the recursive
> > CTE, but using the index on the subsequent selects.
> > 
> > But why? If it uses the index on
> > SELECT MIN(periodizitaet) FROM facttable_imf_ifs WHERE periodizitaet > 
> > 'x'
> > shouldn't it be able to use the same index on 
> > SELECT MIN(periodizitaet) FROM facttable_imf_ifs
> 
> What is the definition of the index facttable_imf_ifs_periodizitaet_idx?

The solution to the puzzle was just 2 paragraphs further down. 

Looks like I have to practice this arc of suspense thing ;-)

hp

-- 
   _  | Peter J. Holzer| A coding theorist is someone who doesn't
|_|_) || think Alice is crazy.
| |   | h...@hjp.at | -- John Gordon
__/   | http://www.hjp.at/ |http://downlode.org/Etext/alicebob.html


signature.asc
Description: Digital signature

Re: [GENERAL] clarification about standby promotion

[Rakesh Kumar]

>> Kill ? You mean "pg_ctl stop -m fast" right ?

Yes.


>Use timeline to resync ? Timeline is an internal mechanism in PostgreSQL, not
>a tool, so I don't get this step...You mean using pg_rewind ?

pg_rewind which uses timeline.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] [Off Topic] Visualizing grouping sets/cubes

[Leonardo M . Ramé]

Hi, I'm reading about Grouping Sets/Rollup/Cube and I wonder which 
js/html5 library allows displaying *easily* (without having to re-format 
it) the returned data from those functions.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Loose indexscan and partial indexes

[Thomas Kellerer]

Peter J. Holzer schrieb am 10.02.2017 um 14:02:
> So it's doing a sequential scan on the initial select in the recursive
> CTE, but using the index on the subsequent selects.
> 
> But why? If it uses the index on
> SELECT MIN(periodizitaet) FROM facttable_imf_ifs WHERE periodizitaet > 'x'
> shouldn't it be able to use the same index on 
> SELECT MIN(periodizitaet) FROM facttable_imf_ifs

What is the definition of the index facttable_imf_ifs_periodizitaet_idx?




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Loose indexscan and partial indexes

[Peter J. Holzer]

I was almost through writing a bug report when I figured out what I was
doing wrong, so I'll post it here in the hope that it prevents someone
from tearing their hair out.

I was trying to use a loose index scan as described on
https://wiki.postgresql.org/wiki/Loose_indexscan on a column with just 3
distinct values in a table with about 20 million rows. To my surprise it
was not faster than a simple “select distinct”, which was doing a
sequential scan.

Here is the plan:

wds=> explain analyze WITH RECURSIVE t AS (
   SELECT MIN(periodizitaet) AS periodizitaet FROM facttable_imf_ifs
   UNION ALL 
   SELECT (SELECT MIN(periodizitaet) FROM facttable_imf_ifs WHERE periodizitaet 
> t.periodizitaet)
   FROM t WHERE t.periodizitaet IS NOT NULL
   )   
SELECT periodizitaet FROM t WHERE periodizitaet IS NOT NULL
;

 QUERY PLAN 


 CTE Scan on t  (cost=993270.41..993272.43 rows=100 width=32) (actual 
time=8073.953..8074.042 rows=3 loops=1)
   Filter: (periodizitaet IS NOT NULL)
   Rows Removed by Filter: 1
   CTE t
 ->  Recursive Union  (cost=993206.56..993270.41 rows=101 width=32) (actual 
time=8073.952..8074.039 rows=4 loops=1)
   ->  Aggregate  (cost=993206.56..993206.57 rows=1 width=2) (actual 
time=8073.948..8073.948 rows=1 loops=1)
 ->  Seq Scan on facttable_imf_ifs facttable_imf_ifs_1  
(cost=0.00..940457.05 rows=21099805 width=2) (actual time=0.258..3567.893 
rows=20649561 loops=1)
   ->  WorkTable Scan on t t_1  (cost=0.00..6.18 rows=10 width=32) 
(actual time=0.021..0.021 rows=1 loops=4)
 Filter: (periodizitaet IS NOT NULL)
 Rows Removed by Filter: 0
 SubPlan 2
   ->  Result  (cost=0.59..0.60 rows=1 width=0) (actual 
time=0.025..0.025 rows=1 loops=3)
 InitPlan 1 (returns $2)
   ->  Limit  (cost=0.44..0.59 rows=1 width=2) (actual 
time=0.024..0.024 rows=1 loops=3)
 ->  Index Only Scan using 
facttable_imf_ifs_periodizitaet_idx on facttable_imf_ifs  
(cost=0.44..1059854.76 rows=7033268 width=2) (actual time=0.022..0.022 rows=1 
loops=3)
   Index Cond: ((periodizitaet IS NOT NULL) 
AND (periodizitaet > t_1.periodizitaet))
   Heap Fetches: 2
 Planning time: 9.261 ms
 Execution time: 8074.219 ms
(19 rows)

So it's doing a sequential scan on the initial select in the recursive
CTE, but using the index on the subsequent selects.

But why? If it uses the index on
SELECT MIN(periodizitaet) FROM facttable_imf_ifs WHERE periodizitaet > 'x'
shouldn't it be able to use the same index on 
SELECT MIN(periodizitaet) FROM facttable_imf_ifs
?

I was playing around a bit with different values and not getting
anywhere, until it hit me:

facttable_imf_ifs_periodizitaet_idx is a partial index with a
“WHERE periodizitaet IS NOT NULL” clause. This is fine for a
“WHERE periodizitaet > 'x'” query, because that implies NOT NULL, but a
straight unadorned “SELECT MIN(periodizitaet)” could return NULL, so it
can't use the index.

Add “where periodizitaet is not null” to the initial query (I'm not
interested in null values, that index is partial for a reason) and all
is well:

wds=> explain analyze WITH RECURSIVE t AS (
   SELECT MIN(periodizitaet) AS periodizitaet FROM facttable_imf_ifs where 
periodizitaet is not null
   UNION ALL 
   SELECT (SELECT MIN(periodizitaet) FROM facttable_imf_ifs WHERE periodizitaet 
> t.periodizitaet)
   FROM t WHERE t.periodizitaet IS NOT NULL
   )   
SELECT periodizitaet FROM t WHERE periodizitaet IS NOT NULL
;

   QUERY PLAN   


 CTE Scan on t  (cost=64.40..66.42 rows=100 width=32) (actual time=0.046..0.704 
rows=3 loops=1)
   Filter: (periodizitaet IS NOT NULL)
   Rows Removed by Filter: 1
   CTE t
 ->  Recursive Union  (cost=0.51..64.40 rows=101 width=32) (actual 
time=0.043..0.698 rows=4 loops=1)
   ->  Result  (cost=0.51..0.52 rows=1 width=0) (actual 
time=0.042..0.042 rows=1 loops=1)
 InitPlan 1 (returns $1)
   ->  Limit  (cost=0.44..0.51 rows=1 width=2) (actual 
time=0.039..0.040 

Re: [GENERAL] Build exclusion constraints USING INDEX

[Steven Winfield]

> I was wondering if there was any way to break down the creation of a new 
> exclusion constraint into stages such that table locks most likely to affect 
> performance during production hours are not taken.
>
> Something like:
>
> CREATE INDEX CONCURRENTLY new_index ON my_table USING gist (column1, column2, 
> column3);
> ALTER TABLE my_table ADD CONSTRAINT my_exclusion_constraint EXCLUDE USING 
> INDEX new_index (column1 WITH &&, column2 WITH =, column3 WITH &&) NOT VALID;
> ALTER TABLE my_table VALIDATE CONSTRAINT my_exclusion_constraint;
>
> AFAICT nothing like the second statement is currently available, but I wanted 
> to check that and see if there are any workarounds.

I'm guessing there aren't any workarounds then, and exclusion constraints will 
lock the whole table against writes while they are being created?

Steve.

Re: [GENERAL] clarification about standby promotion

[Jehan-Guillaume de Rorthais]

On Thu, 9 Feb 2017 18:27:30 +
Rakesh Kumar  wrote:

> >Sure, but when you are doing a switchover, the standby is supposed to be
> >connected to the master when you shutdown the master. So based on the doc,
> >the standby should receive **everything** from the master before the master
> >actually shutdown.  
> 
> We use 9.5 and even in that version there is no handshake during role
> reversal. In fact PG does not have concept of handshake and role reversal
> unlike in Db2, oracle and sqlserver you can switchover from one to other by a
> single command.
> 
> Our DBAs use home grown script for switchover which does the following:
> 
> 1 - first kill postmaster in the outgoing primary.

Kill ? You mean "pg_ctl stop -m fast" right ?

> 2 - promote the standby as the new primary
> 3 - use timeline to resync former primary (of step 1) with the new primary
> (step 2). 

Use timeline to resync ? Timeline is an internal mechanism in PostgreSQL, not
a tool, so I don't get this step...You mean using pg_rewind ?

So far, I stick to my procedure (given in another answer) which looks a lot
more safer.

> I hope a more elegant way exists as in other RDBMS.

Me too. But it require a lot of work as a master is not able to "demote" as a
standby without a restart. As far as I know, the standby code path is only
accessible during startup.

Note that you could switchover in one command as well using external tools like
PAF [1][2]. But PAF comes with a lot more features than just switchover and
rely on Pacemaker...

[1] https://github.com/dalibo/PAF
[2] http://www.dalibo.org/_media/2016-pgconfeu-paf.html.gz

Cheers,


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL on eMMC - Corrupt file system

[Mark Morgan Lloyd]

On 09/02/17 23:00, Christoph Moench-Tegeder wrote:

## Thomas Güttler (guettl...@thomas-guettler.de):


 Is running linux with postgres on eMMC a bad idea in general?


I'd say that running anything with a read-write load on eMMC will
end in pieces. It's ok to occasionally write something, but a mixed
load is not really what these things were designed for. The wear
leveling can be quite basic, you never know when it's gonna happen
(i.e. sudden power down can kill your filesystem - that's why disabling
journaling is not a very great idea), and if your device is "mostly
full" anyways, the wear leveling has not much space to redirect the
writes to. Remember that some of those chips are sold mostly by
price - that is, the hobbyist "embedded" devices get the cheapest
chips. A safer bet would be adding an external storage; some
64GB SATA SSDs are available for less than 50€ (perhaps it's better
not to go for the cheapest ones here, too).


I agree, but three additional comments. First, we've got a fair number 
of RPis running their root filesystems on the internal SD-Card without 
problems, but the one Odroid which runs an eMMC card failed a few weeks 
ago. Second, a useful precaution is to put stuff which will be updated 
on an external device, although the same longevity concerns apply if 
it's Flash-based. Third, experience here suggests that reliability 
/might/ be improved if you fully zero a device before partitioning it to 
make absolutely sure that the internal controller has touched every block.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general