Creating a function

[Glenn Schultz]

Hi,

I am trying to create a function to bin based on user value and I am
stuck.  I followed the
postgres create function tutoriall but I am missing something.  Any help
would be appreciated as I think I am just going further off course at this
point

Glenn

CREATE FUNCTION "IncentiveBin"(in Gwac double precision,
 in MtgRaate double precision,
 in BinSize double precision)
  RETURNS double precision
LANGUAGE 'sql'
VOLATILE PARALLEL SAFE
AS
$function$
BEGIN
ceiling((Gwac - MtgRate)/BinSize) * BinSize;
END
$function$

Re: Creating a function

[Pavel Křehula]

Hi,
something like this?

create or replace FUNCTION "IncentiveBin"(in Gwac double precision,
in MtgRaate double precision,
in BinSize double precision)
  RETURNS double precision
LANGUAGE sql
VOLATILE PARALLEL SAFE
as 'select ceiling(($1 - $2)/$3) * $3;';

select "IncentiveBin"(1000.0,9,212);

Pavel

Dne 28.11.2018 10:35:09, "Glenn Schultz"  napsal:


Hi,

I am trying to create a function to bin based on user value and I am 
stuck.  I followed the
postgres create function tutoriall but I am missing something.  Any 
help would be appreciated as I think I am just going further off course 
at this point


Glenn

CREATE FUNCTION "IncentiveBin"(in Gwac double precision,
 in MtgRaate double precision,
 in BinSize double precision)
  RETURNS double precision
LANGUAGE 'sql'
VOLATILE PARALLEL SAFE
AS
$function$
BEGIN
ceiling((Gwac - MtgRate)/BinSize) * BinSize;
END
$function$

Regarding Tds_fdw

[Durgamahesh Manne]

Hi

Respected community members

I have configured tds_fdw on postgres server.. I have created multiple
foreign tables related to sql server as of now i could run select queries
with out any issues

  i got  this error ERROR: cannot insert into foreign table "pgsql"  when i
tried to insert the data into foerign table

1) Is there any way to run insert delete update  queries on foreign tables ?



Regards

durgamahesh manne

Re: Regarding Tds_fdw

[Thomas Kellerer]

Durgamahesh Manne schrieb am 28.11.2018 um 11:28:
> I have configured tds_fdw on postgres server.. I have created
> multiple foreign tables related to sql server as of now i could run
> select queries with out any issues
> 
>   i got  this error ERROR: cannot insert into foreign table "pgsql"  when i 
> tried to insert the data into foerign table 
> 
> 1) Is there any way to run insert delete update  queries on foreign tables ?

The TDS FDW currently does currently not support write operations:

Quote from https://github.com/tds-fdw/tds_fdw

   The current version does not yet support JOIN push-down, or write operations.

Re: Regarding Tds_fdw

[Pavel Stehule]

Hi

st 28. 11. 2018 v 11:28 odesílatel Durgamahesh Manne <
maheshpostgr...@gmail.com> napsal:

> Hi
>
> Respected community members
>
> I have configured tds_fdw on postgres server.. I have created multiple
> foreign tables related to sql server as of now i could run select queries
> with out any issues
>
>   i got  this error ERROR: cannot insert into foreign table "pgsql"  when
> i tried to insert the data into foerign table
>
> 1) Is there any way to run insert delete update  queries on foreign tables
> ?
>

I can read on page
https://support.google.com/hangouts/answer/3110347?hl=en&ref_topic=2944918&vid=0-737329123936-1543400907610

"The current version does not yet support JOIN push-down, or write
operations."

Regards

Pavel


>
>
>
> Regards
>
> durgamahesh manne
>

Re: Regarding Tds_fdw

[Durgamahesh Manne]

On Wed, Nov 28, 2018 at 4:22 PM Pavel Stehule 
wrote:

> Hi
>
> st 28. 11. 2018 v 11:28 odesílatel Durgamahesh Manne <
> maheshpostgr...@gmail.com> napsal:
>
>> Hi
>>
>> Respected community members
>>
>> I have configured tds_fdw on postgres server.. I have created multiple
>> foreign tables related to sql server as of now i could run select queries
>> with out any issues
>>
>>   i got  this error ERROR: cannot insert into foreign table "pgsql"  when
>> i tried to insert the data into foerign table
>>
>> 1) Is there any way to run insert delete update  queries on foreign
>> tables ?
>>
>
> I can read on page
> https://support.google.com/hangouts/answer/3110347?hl=en&ref_topic=2944918&vid=0-737329123936-1543400907610
>
> "The current version does not yet support JOIN push-down, or write
> operations."
>
> Regards
>
> Pavel
>
>
>>
>>
>>
>> Regards
>>
>> durgamahesh manne
>>
>


 Hi

are there any fdw which supports dml operation on  sql server foreign
tables i have created in pg server ?



Regards

Re: Regarding Tds_fdw

[Durgamahesh Manne]

On Wed, Nov 28, 2018 at 6:31 PM Durgamahesh Manne 
wrote:

>
>
> On Wed, Nov 28, 2018 at 4:22 PM Pavel Stehule 
> wrote:
>
>> Hi
>>
>> st 28. 11. 2018 v 11:28 odesílatel Durgamahesh Manne <
>> maheshpostgr...@gmail.com> napsal:
>>
>>> Hi
>>>
>>> Respected community members
>>>
>>> I have configured tds_fdw on postgres server.. I have created multiple
>>> foreign tables related to sql server as of now i could run select queries
>>> with out any issues
>>>
>>>   i got  this error ERROR: cannot insert into foreign table "pgsql"
>>> when i tried to insert the data into foerign table
>>>
>>> 1) Is there any way to run insert delete update  queries on foreign
>>> tables ?
>>>
>>
>> I can read on page
>> https://support.google.com/hangouts/answer/3110347?hl=en&ref_topic=2944918&vid=0-737329123936-1543400907610
>>
>> "The current version does not yet support JOIN push-down, or write
>> operations."
>>
>> Regards
>>
>> Pavel
>>
>>
>>>
>>>
>>>
>>> Regards
>>>
>>> durgamahesh manne
>>>
>>
>
>
>  Hi
>
> are there any fdws which supports dml operation on  sql server foreign
> tables i have created in pg server ?
>
>
>
> Regards
>
>
>

2018-11-28 10:40:01,906 9672 CRITICAL ? odoo.service.server: Failed to initialize database `abc`

[pavan95]

Hello Community,

Anyone has configured odoo with postgres? Please guide me with the below
error.

We have been experiencing an error while configuring the odoo(odoo 11.0) 
application to the postgres(postgres 10.5) database. We have done the
configuration in two approaches.

First Approach:

All the objects in the database 'abc' are defined in the "public" schema. In
this approach, the application is configured successfully without errors.

Second Approach:

The objects in the database "abc" are moved to custom schema named "xyz". 
Then we're facing this error "2018-11-28 10:40:01,906 9672 CRITICAL ?
odoo.service.server: Failed to initialize database `abc`" . In detail it is
throwing the error "relation ir_%%  exists".

Please help us to cope up with this error. 

Looking forward to hear from you. Thanks in Advance!.



Regards,

Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: 2018-11-28 10:40:01,906 9672 CRITICAL ? odoo.service.server: Failed to initialize database `abc`

[Adrian Klaver]

On 11/28/18 7:16 AM, pavan95 wrote:

Hello Community,

Anyone has configured odoo with postgres? Please guide me with the below
error.

We have been experiencing an error while configuring the odoo(odoo 11.0)
application to the postgres(postgres 10.5) database. We have done the
configuration in two approaches.

First Approach:

All the objects in the database 'abc' are defined in the "public" schema. In
this approach, the application is configured successfully without errors.

Second Approach:

The objects in the database "abc" are moved to custom schema named "xyz".
Then we're facing this error "2018-11-28 10:40:01,906 9672 CRITICAL ?
odoo.service.server: Failed to initialize database `abc`" . In detail it is
throwing the error "relation ir_%%  exists".

Please help us to cope up with this error.


At a guess the ORM is configured to look in public.*

To find out if there is a way to change this I would suggest the Odoo forum:

https://www.odoo.com/forum/help-1



Looking forward to hear from you. Thanks in Advance!.



Regards,

Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





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

Re: 2018-11-28 10:40:01,906 9672 CRITICAL ? odoo.service.server: Failed to initialize database `abc`

[Andreas Kretschmer]

Am 28.11.2018 um 16:16 schrieb pavan95:

The objects in the database "abc" are moved to custom schema named "xyz".
Then we're facing this error "2018-11-28 10:40:01,906 9672 CRITICAL ?
odoo.service.server: Failed to initialize database `abc`" . In detail it is
throwing the error "relation ir_%%  exists".


have you checked if this relation exists? Have you set search_path to 
xyz instead of the default public?



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Force Reconnect of streaming replication

[Hannes Erven]

Hi,


consider a PG10 master center, streaming via internet to another site. The 
receiving site has a fast primary uplink and much slower backup link.

When the primary link goes down, all traffic is routed through the backup 
connection. The streaming replication's connection drops and is automatically 
re-establish over the backup link.

But when the primary link comes up again, the replication will still use the 
already established connection via the slow backup link.


If I kill the wal_sender process on the master, the slave will immediately 
reconnect over the "best" available link.
But is this really the best way to force PG to re-connect the replication?

I guess I could as well kill the wal-receiving process on the slave but still 
I'm hesitating to put something like "killall pg_walsender" in a cronjob...


Is there a better way?


Thanks for your opinions,
best regards

-hannes

Primary Key index with Include

[PegoraroF10]

As you know version 11 gives us the possibility to include aditional columns
to an index.
So, if I recreate all my primary key indexes to contain a description
column, like ...

CREATE UNIQUE INDEX CONCURRENTLY Products_pk ON Products(Product_ID)
INCLUDE(Name);
ALTER TABLE Products ADD CONSTRAINT Products_pk PRIMARY KEY(Product_ID)
USING INDEX Products_pk

Then, when I do
select Item_ID, Product_ID, Name from Items inner join Products
using(Product_ID)

I thought It would do a index scan only for Products table, but it does not,
why ?
What do I need do to use index scan only when searching Product_ID and Name
on table Products ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Primary Key index with Include

[Adrian Klaver]

On 11/28/18 11:55 AM, PegoraroF10 wrote:

As you know version 11 gives us the possibility to include aditional columns
to an index.
So, if I recreate all my primary key indexes to contain a description
column, like ...

CREATE UNIQUE INDEX CONCURRENTLY Products_pk ON Products(Product_ID)
INCLUDE(Name);
ALTER TABLE Products ADD CONSTRAINT Products_pk PRIMARY KEY(Product_ID)
USING INDEX Products_pk

Then, when I do
select Item_ID, Product_ID, Name from Items inner join Products
using(Product_ID)

I thought It would do a index scan only for Products table, but it does not,
why ?


Because an index is overhead and depending on the situation the planner 
may determine it is better not to use it.


Post the results of:

EXPLAIN ANALYZE select Item_ID, Product_ID, Name from Items inner join 
Products using(Product_ID);


That will help understand what the planner is doing.



What do I need do to use index scan only when searching Product_ID and Name
on table Products ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





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

surprising query optimisation

[Chris Withers]

Hi All,

We have an app that deals with a lot of queries, and we've been slowly 
seeing performance issues emerge. We take a lot of free form queries 
from users and stumbled upon a very surprising optimisation.


So, we have a 'state' column which is a 3 character string column with 
an index on it. Despite being a string, this column is only used to 
store one of three values: 'NEW', 'ACK', or 'RSV'.


One of our most common queries clauses is "state!='RSV'" and we've found 
that by substituting this clause with "state='ACK' or state='NEW'" 
wherever it was used, we've dropped the postgres server's load average 
from 20 down to 4 and the CPU usage from 60% in user space down to <5%.


This seems counter-intuitive to me, so thought I'd ask here. Why would 
this be likely to make such a difference? We're currently on 9.4, is 
this something that's likely to be different (better? worse?) if we got 
all the way up to 10 or 11?


cheers,

Chris

Re: surprising query optimisation

[Adrian Klaver]

On 11/28/18 2:26 PM, Chris Withers wrote:

Hi All,

We have an app that deals with a lot of queries, and we've been slowly 
seeing performance issues emerge. We take a lot of free form queries 
from users and stumbled upon a very surprising optimisation.


So, we have a 'state' column which is a 3 character string column with 
an index on it. Despite being a string, this column is only used to 
store one of three values: 'NEW', 'ACK', or 'RSV'.


One of our most common queries clauses is "state!='RSV'" and we've found 
that by substituting this clause with "state='ACK' or state='NEW'" 
wherever it was used, we've dropped the postgres server's load average 
from 20 down to 4 and the CPU usage from 60% in user space down to <5%.


This seems counter-intuitive to me, so thought I'd ask here. Why would 


The way I see it is state = "something" is a confined question. state != 
'something' is potentially unbounded.


Does EXPLAIN ANALYZE shed any light?

this be likely to make such a difference? We're currently on 9.4, is 
this something that's likely to be different (better? worse?) if we got 
all the way up to 10 or 11?


cheers,

Chris





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

Re: surprising query optimisation

[Gavin Flower]

On 29/11/2018 11:26, Chris Withers wrote:

Hi All,

We have an app that deals with a lot of queries, and we've been slowly 
seeing performance issues emerge. We take a lot of free form queries 
from users and stumbled upon a very surprising optimisation.


So, we have a 'state' column which is a 3 character string column with 
an index on it. Despite being a string, this column is only used to 
store one of three values: 'NEW', 'ACK', or 'RSV'.


One of our most common queries clauses is "state!='RSV'" and we've 
found that by substituting this clause with "state='ACK' or 
state='NEW'" wherever it was used, we've dropped the postgres server's 
load average from 20 down to 4 and the CPU usage from 60% in user 
space down to <5%.


This seems counter-intuitive to me, so thought I'd ask here. Why would 
this be likely to make such a difference? We're currently on 9.4, is 
this something that's likely to be different (better? worse?) if we 
got all the way up to 10 or 11?


cheers,

Chris



At a guess...

    "state!='RSV'"  ==> pg only has to check one value

and

    "state='ACK' or state='NEW'"   ==> pg has to check two values

so I would expect the '!=' to be faster.


Cheers,
Gavin

Re: surprising query optimisation

[Stephen Frost]

Greetings,

* Chris Withers (ch...@withers.org) wrote:
> We have an app that deals with a lot of queries, and we've been slowly
> seeing performance issues emerge. We take a lot of free form queries from
> users and stumbled upon a very surprising optimisation.
> 
> So, we have a 'state' column which is a 3 character string column with an
> index on it. Despite being a string, this column is only used to store one
> of three values: 'NEW', 'ACK', or 'RSV'.

Sounds like a horrible field to have an index on.

> One of our most common queries clauses is "state!='RSV'" and we've found
> that by substituting this clause with "state='ACK' or state='NEW'" wherever
> it was used, we've dropped the postgres server's load average from 20 down
> to 4 and the CPU usage from 60% in user space down to <5%.

You've changed the question you're asking the database.  PG doesn't
*know* that there's only those three values, but it probably has a
pretty good guess about how many records are ACK and how many are NEW
thanks to those being in the MCV list.

> This seems counter-intuitive to me, so thought I'd ask here. Why would this
> be likely to make such a difference? We're currently on 9.4, is this
> something that's likely to be different (better? worse?) if we got all the
> way up to 10 or 11?

When you change what you're asking, PG is going to change how it gives
you the answer and sometimes that'll be faster and other times it won't
be.

Really though, if you want something more than wild speculation, posting
the 'explain analyze' of each query along with the actual table
definitions and sizes and such would be the best way to get it.

I'd suggest you check out the wiki article written about this kind of
question:

https://wiki.postgresql.org/wiki/Slow_Query_Questions

Thanks!

Stephen


signature.asc
Description: PGP signature

The age() of a column?

[Ron]

What does it mean, and where is it documented?  The only place in the docs I 
see reference to age() is 
https://www.postgresql.org/docs/9.6/functions-datetime.html and this doesn't 
seem to fit that use case.


Thanks

TAPb=# select COALESCE(ta.schemaname, 'pg_toast') || '.' || cl.relname,
   cl.relfrozenxid,
   age(cl.relfrozenxid)
from pg_class cl FULL JOIN pg_tables ta
    ON ta.tablename = cl.relname
where not cl.relfrozenxid = xid '0'
  --and age(cl.relfrozenxid) > 400
order by 1
limit 30;
   ?column?    | relfrozenxid |   age
---+--+-
cds.ach_return_batch   | 58569152 | 2111005
cds.ach_return_detail  | 58569152 | 2111005
cds.all_day_event  | 58569152 | 2111005
cds.all_day_event_trigger  | 58569152 | 2111005
[snip]
cds.bank_ftp   | 58569152 | 2111005
cds.bank_health_care_job   | 58569152 | 2111005
cds.bank_inbound_file  | 58569152 | 2111005
cds.bank_input_format  | 58569152 | 2111005
(30 rows)


--
Angular momentum makes the world go 'round.

Re: The age() of a column?

[Tom Lane]

Ron  writes:
> What does it mean, and where is it documented?

age(xid) returns the difference between the given xid and the current xid.
It's not terribly well documented, but psql's \df gives some info:

regression=# \df+ age 
   Schema   | Name | Result data type |   Argument data types   
 | Type | Volatility |  Parallel  |  Owner   | Security | 
Access privileges | Language | Source code  
| Description   
  
...
 pg_catalog | age  | integer  | xid 
 | func | stable | restricted | postgres | invoker  |   
| internal | xid_age
  | age of a transaction ID, in transactions before current 
transaction

The only reference I could find in the SGML docs is in the maintenance
chapter.

regards, tom lane

Re: tsvector 256 limit and matching text with common words

[Where is Where]

> Hello, I am wondering if it is possible to increase or remove the
> '256 positions per lexeme' limit?
>
> We need to search words with common words like 'the' 'a' in full text. Or
> is there workaround for it? Thanks!
>

ps: this has not been posted on other list.

Re: The age() of a column?

[Adrian Klaver]

On 11/28/18 7:32 PM, Ron wrote:
What does it mean, and where is it documented?  The only place in the 


https://www.postgresql.org/docs/10/routine-vacuuming.html

"The age column measures the number of transactions from the cutoff XID 
to the current transaction's XID."


docs I see reference to age() is 
https://www.postgresql.org/docs/9.6/functions-datetime.html and this 
doesn't seem to fit that use case.


Thanks

TAPb=# select COALESCE(ta.schemaname, 'pg_toast') || '.' || cl.relname,
    cl.relfrozenxid,
    age(cl.relfrozenxid)
from pg_class cl FULL JOIN pg_tables ta
     ON ta.tablename = cl.relname
where not cl.relfrozenxid = xid '0'
   --and age(cl.relfrozenxid) > 400
order by 1
limit 30;
    ?column?    | relfrozenxid |   age
---+--+-
cds.ach_return_batch   | 58569152 | 2111005
cds.ach_return_detail  | 58569152 | 2111005
cds.all_day_event  | 58569152 | 2111005
cds.all_day_event_trigger  | 58569152 | 2111005
[snip]
cds.bank_ftp   | 58569152 | 2111005
cds.bank_health_care_job   | 58569152 | 2111005
cds.bank_inbound_file  | 58569152 | 2111005
cds.bank_input_format  | 58569152 | 2111005
(30 rows)





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

Triggers when importing data

[Sathish Kumar]

Hi,

I am trying to export and import sql file of a database. I would like
to know whether it will execute all the triggers when importing the
sql dump which is for Insert or Update or Delete.

Export:pg_dump -h test -U db_admin --format=plain --no-owner --no-acl
production | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1
EXTENSION/g' > test.sql

Import: psql -h test -U db_admin -d production -W < test.sql

Re: Triggers when importing data

[Laurenz Albe]

Sathish Kumar wrote:
> I am trying to export and import sql file of a database. I would like to know
> whether it will execute all the triggers when importing the sql dump which
> is for Insert or Update or Delete.
> 
> Export:pg_dump -h test -U db_admin --format=plain --no-owner --no-acl 
> production | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 
> EXTENSION/g' > test.sql
> 
> Import: psql -h test -U db_admin -d production -W < test.sql

That will call all triggers on tables that have no primary key
or unique constraints.

On all other tables, that will give you a constraint violation because
the same rows are already in the table...

What are you trying to achieve?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Reg: Query for DB growth size

[R.R. PRAVEEN RAJA]

Hi All,

Can i get the query or steps for how to check the Database growth in postgres. 
Thanks in advance.

Regards,
Praveen

Sv: Reg: Query for DB growth size

[Andreas Joseph Krogh]

På torsdag 29. november 2018 kl. 07:41:24, skrev R.R. PRAVEEN RAJA <
rrpraveenr...@live.com >:
Hi All,
 
Can i get the query or steps for how to check the Database growth in postgres. 
Thanks in advance.
 
select pg_size_pretty(pg_database_size(current_database()));
  
-- Andreas Joseph Krogh