postgresql-10 armhf repository

[Grégoire Berclaz]

Hello,

I would like to install postgresql-10 on raspberries. I followed this 
instructions : https://wiki.postgresql.org/wiki/Apt


Unfortunatly after a "sudo apt-get update" I get the error :
" Skipping acquire of configured file 'main/binary-armhf/Packages' as 
repository 'http://apt.postgresql.org/pub/repos/apt stretch-pgdg 
InRelease' doesn't support architecture 'armhf' "


The problem is that there is no armhf directory on the official 
repository https://apt.postgresql.org/pub/repos/apt/dists/stretch-pgdg/main/


So, I have two questions :

 * Why postgresql.org doesn't have this package on the repository ?
 * Is there any other repository with the last version of postgresql-10
   armhf packages ?


Regards,
Greg

Re: Command line session and running queries

[Laurenz Albe]

imp3 wrote:
> I've run an update query inside command line tool (psql) then I've exit the 
> tool (closing the window).
> 
> When I re-logged in, I've noticed in pg_stat_activity that my query was still 
> running.
> I used the command pg_cancel_backend with the right PID, but it returned "f" 
> few seconds later.
> I re-checked pg_stat_activity and I was not able to see my query; there was 
> only an autovacuum running on the table impacted by my query.
> Since my update query usually takes about one hour, I was wandering what 
> happened.
> 
> Is postgres regularly checking command line session? If session has expired, 
> would postgres stop the query and rollback it?
> 
> I'm using both client and server v10.1.

The query probably endet by itself between the point in time when you saw
it in pg_stat_activity and when you tried to cancel it.

The PostgreSQL backend will only notice that the client has died when it
is done processing the query and tries to send an answer to the client.

If that is a regular problem for you, you could set "statement_timeout"
to put an upper limit on "runaway" queries.

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

Re: pg_sample

[naveen12]

i have installed postgresql on centos.
i want to use pg_sample.

Do i need to install that separately of it's included in the postgresql



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

Re: postgresql-10 armhf repository

[Imre Samu]

> Is there any other repository with the last version of postgresql-10
armhf packages ?

Postgre Docker images (alpine based)
 -arm32v5:https://hub.docker.com/r/arm32v5/postgres/
- arm32v6:   https://hub.docker.com/r/arm32v6/postgres/   (for Raspberry PI
1 )
- arm32v7:   https://hub.docker.com/r/arm32v7/postgres/   (for Raspberry PI
2, Allwinner A10, A20, H3).
- arm64v8:   https://hub.docker.com/r/arm64v8/postgres/

Imre

Grégoire Berclaz  ezt írta (időpont: 2018.
aug. 23., Cs, 10:43):

> Hello,
>
> I would like to install postgresql-10 on raspberries. I followed this
> instructions : https://wiki.postgresql.org/wiki/Apt
>
> Unfortunatly after a "sudo apt-get update" I get the error :
> " Skipping acquire of configured file 'main/binary-armhf/Packages' as
> repository 'http://apt.postgresql.org/pub/repos/apt stretch-pgdg
> InRelease' doesn't support architecture 'armhf' "
>
> The problem is that there is no armhf directory on the official repository
> https://apt.postgresql.org/pub/repos/apt/dists/stretch-pgdg/main/
>
> So, I have two questions :
>
>- Why postgresql.org doesn't have this package on the repository ?
>- Is there any other repository with the last version of postgresql-10
>armhf packages ?
>
>
> Regards,
> Greg
>

Upgrade/Downgrade

[Sonam Sharma]

Hello,

My postgres version is 10.4 and I want to downgrade it to 9.5.
and one is at 9.2 and need to upgrade it to 9.5.
Can someone please help how to do this.
Unable to find anything online

Re: Upgrade/Downgrade

[Andreas Kretschmer]

On 23 August 2018 12:51:08 CEST, Sonam Sharma  wrote:
>Hello,
>
>My postgres version is 10.4 and I want to downgrade it to 9.5.
>and one is at 9.2 and need to upgrade it to 9.5.
>Can someone please help how to do this.
>Unable to find anything online

Why downgrade? Dump and restore should do the job, have you tried it?

Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

MultiXactId 24018 has not been created yet -- apparent wraparound error

[Dean Armada]

My team is confused as we are not doing anything then we experienced this
in our staging environment. It's happening only in one of our tables. When
we try fetch in that table, the message below returns

project=# SELECT * FROM table_table;
ERROR:  MultiXactId 24018 has not been created yet -- apparent wraparound

Please take note that I am using docker containers and my image is
postgres:9.6.3-alpine
Regards,
Dean

Re: Upgrade/Downgrade

[Sonam Sharma]

No, I didn't. The version is not compatible with application, so need to
downgrade it

On Thu, Aug 23, 2018, 4:56 PM Andreas Kretschmer 
wrote:

> On 23 August 2018 12:51:08 CEST, Sonam Sharma 
> wrote:
> >Hello,
> >
> >My postgres version is 10.4 and I want to downgrade it to 9.5.
> >and one is at 9.2 and need to upgrade it to 9.5.
> >Can someone please help how to do this.
> >Unable to find anything online
>
> Why downgrade? Dump and restore should do the job, have you tried it?
>
> Regards, Andreas
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company
>
>

Re: Upgrade/Downgrade

[Achilleas Mantzios]

On 23/08/2018 14:30, Sonam Sharma wrote:

No, I didn't. The version is not compatible with application, so need to 
downgrade it


Have you tried to tweak postgresql.conf ?
What do you mean is not compatible? What's the problem?



On Thu, Aug 23, 2018, 4:56 PM Andreas Kretschmer mailto:andr...@a-kretschmer.de>> wrote:

On 23 August 2018 12:51:08 CEST, Sonam Sharma mailto:sonams1...@gmail.com>> wrote:
>Hello,
>
>My postgres version is 10.4 and I want to downgrade it to 9.5.
>and one is at 9.2 and need to upgrade it to 9.5.
>Can someone please help how to do this.
>Unable to find anything online

Why downgrade? Dump and restore should do the job, have you tried it?

Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: pg_sample

[Adrian Klaver]

On 08/23/2018 12:10 AM, naveen12 wrote:

i have installed postgresql on centos.
i want to use pg_sample.

Do i need to install that separately of it's included in the postgresql


It is not included in the Postgres core or contrib packages. You will 
need to install it separately.






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





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

Re: "checkpointer process" is consuming more memory. How to control it?

[Andres Freund]

On 2018-08-22 11:14:48 -0300, Avi Vallarapu wrote:
> Seams like you are with the default or fairly less checkpoint_* settings.
> 
> You need to look at several patterns to understand the best values for
> these settings.
> 
> For now, i can say 300 seconds for checkpoint_timeout could be fairly very
> less. Try increasing that with a reload.
> 
> See this =>
> https://www.percona.com/blog/2018/06/15/tuning-postgresql-for-sysbench-tpcc/
> 
> Please do note that delaying checkpointing could also increase the time for
> crash recovery/shutdown, etc.

This seems to have not that much to do with the problem the OP has, no?

I suggest just using pmap -d $process_id_of_checkpointer
that'll likely show that it doesn't actually use much memory, but that
the RSS originates from the shared memory it has touched.

- Andres

Re: MultiXactId 24018 has not been created yet -- apparent wraparound error

[Alvaro Herrera]

On 2018-Aug-23, Dean Armada wrote:

> My team is confused as we are not doing anything then we experienced this
> in our staging environment. It's happening only in one of our tables. When
> we try fetch in that table, the message below returns
> 
> project=# SELECT * FROM table_table;
> ERROR:  MultiXactId 24018 has not been created yet -- apparent wraparound
> 
> Please take note that I am using docker containers and my image is
> postgres:9.6.3-alpine

Quite a few bugs in this area have been fixed in releases after 9.6.3.
I suggest an upgrade (9.6.10 is latest in that branch), and report back
if it still happens.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pg_sample

[Naveen Dabas]

sir from where should i install it.
I tried but  i didn't found separate  link for pg_sample
can you help me in this


thanks

On Thu, Aug 23, 2018 at 6:34 PM, Adrian Klaver 
wrote:

> On 08/23/2018 12:10 AM, naveen12 wrote:
>
>> i have installed postgresql on centos.
>> i want to use pg_sample.
>>
>> Do i need to install that separately of it's included in the postgresql
>>
>
> It is not included in the Postgres core or contrib packages. You will need
> to install it separately.
>
>
>>
>>
>> --
>> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f184378
>> 0.html
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
--
With Regards
Naveen Dabas
Ph. 9017298370

-- 
*Important Disclaimer:* Information contained in this email is for the 
recipient primarily addressed to. If you are not the primary recipient or 
are not supposed to receive this email, you are advised to kindly delete 
the email or the thread and notify of the error. The logo is a registered 
and copyrighted property of *ACTAS TECHNOLOGIES PRIVATE LIMITED*. Do not 
use it without authorization.

Re: pg_sample

[Adrian Klaver]

On 08/23/2018 07:39 AM, Naveen Dabas wrote:

sir from where should i install it.
I tried but  i didn't found separate  link for pg_sample
can you help me in this


I am guessing it is this:

https://github.com/mla/pg_sample




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

Re: pg_sample

[Adrian Klaver]

On 08/23/2018 07:39 AM, Naveen Dabas wrote:

sir from where should i install it.
I tried but  i didn't found separate  link for pg_sample
can you help me in this



Something similar:

https://github.com/18F/rdbms-subsetter

It is Python based and can be pip installed.




thanks





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

Re: Upgrade/Downgrade

[Dmitri Maziuk]

On Thu, 23 Aug 2018 14:47:25 +0300
Achilleas Mantzios  wrote:

> What do you mean is not compatible? What's the problem?

As I recall there's several SETs and some other stuff that v.10 spits out that 
earlier versions do not recognize. Nothing that a little sed script can't fix 
but you need to understand what you're doing.

-- 
Dmitri Maziuk 

extracting the sql for a function

[Paul Tilles]

Using postgres Version 9.5

I can extract the sql for a table using

pg_dump  -d database_name  -s  -t  table_name  -f  table_name.sql

Is there something equivalent for extracting the sql for a function from 
the database?



Paul Tilles

RE: extracting the sql for a function

[Igor Neyman]

-Original Message-
From: Paul Tilles [mailto:paul.til...@noaa.gov] 
Sent: Thursday, August 23, 2018 1:18 PM
To: pgsql-gene...@postgresql.org
Subject: extracting the sql for a function

WARNING: This email originated from outside of Perceptron! Please be mindful of 
PHISHING and MALWARE risks.

Using postgres Version 9.5

I can extract the sql for a table using

pg_dump  -d database_name  -s  -t  table_name  -f  table_name.sql

Is there something equivalent for extracting the sql for a function from the 
database?


Paul Tilles

__

select prosrc from pg_proc where proname = 'function_name';

You can pronamespace to WHERE clause to specify schema.

Regards,
Igor Neyman

Re: Upgrade/Downgrade

[Joshua D. Drake]

On 08/23/2018 04:47 AM, Achilleas Mantzios wrote:

On 23/08/2018 14:30, Sonam Sharma wrote:
No, I didn't. The version is not compatible with application, so need 
to downgrade it


Have you tried to tweak postgresql.conf ?
What do you mean is not compatible? What's the problem?


My guess would be that they have a vendor supported application that is 
*only* supported on 9.5. I run into this more often than I like.


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

RE: extracting the sql for a function

[Charles Clavadetscher]

Hello

> -Original Message-
> From: Paul Tilles [mailto:paul.til...@noaa.gov]
> Sent: Donnerstag, 23. August 2018 19:18
> To: pgsql-gene...@postgresql.org
> Subject: extracting the sql for a function
> 
> Using postgres Version 9.5
> 
> I can extract the sql for a table using
> 
> pg_dump  -d database_name  -s  -t  table_name  -f  table_name.sql
> 
> Is there something equivalent for extracting the sql for a function from the 
> database?
> 
> 
> Paul Tilles
> 

I don't know if there is but you can get the function definition:

SELECT * FROM pg_get_functiondef('functionname'::regproc);

or

SELECT * FROM pg_get_functiondef('functionname(list of parameters data 
types)'::regprocedure);

If you want to export you may use the -t and -o switches.

Regards
Charles

Re: extracting the sql for a function

[Melvin Davidson]

*>is there something equivalent for extracting the sql for a function from
the database? *

*Have a look at pg_extractor. It is essentially a pg_dump but with many
more options.*

*https://github.com/omniti-labs/pg_extractor
*

On Thu, Aug 23, 2018 at 2:53 PM Charles Clavadetscher <
clavadetsc...@swisspug.org> wrote:

> Hello
>
> > -Original Message-
> > From: Paul Tilles [mailto:paul.til...@noaa.gov]
> > Sent: Donnerstag, 23. August 2018 19:18
> > To: pgsql-gene...@postgresql.org
> > Subject: extracting the sql for a function
> >
> > Using postgres Version 9.5
> >
> > I can extract the sql for a table using
> >
> > pg_dump  -d database_name  -s  -t  table_name  -f  table_name.sql
> >
> > Is there something equivalent for extracting the sql for a function from
> the database?
> >
> >
> > Paul Tilles
> >
>
> I don't know if there is but you can get the function definition:
>
> SELECT * FROM pg_get_functiondef('functionname'::regproc);
>
> or
>
> SELECT * FROM pg_get_functiondef('functionname(list of parameters data
> types)'::regprocedure);
>
> If you want to export you may use the -t and -o switches.
>
> Regards
> Charles
>
>
>
>

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Upgrade/Downgrade

[Nikolay Samokhvalov]

On Thu, Aug 23, 2018 at 11:44 AM Joshua D. Drake 
wrote:

> On 08/23/2018 04:47 AM, Achilleas Mantzios wrote:
> > On 23/08/2018 14:30, Sonam Sharma wrote:
> >> No, I didn't. The version is not compatible with application, so need
> >> to downgrade it
> >
> > Have you tried to tweak postgresql.conf ?
> > What do you mean is not compatible? What's the problem?
>
> My guess would be that they have a vendor supported application that is
> *only* supported on 9.5. I run into this more often than I like.
>

Same story.

I suppose it's related (at least partially) xlog->wal and
**_location->**_lsn renamings in function names (full list made in Postgres
10:
https://wiki.postgresql.org/wiki/New_in_postgres_10#Renaming_of_.22xlog.22_to_.22wal.22_Globally_.28and_location.2Flsn.29
)
Those 10's changes are really painful.

I wish there were be some grace period, say 2-3 years, when both pg*_xlog_*
function names would also work as aliases for pg*_wal_**.

BTW, if the reason of downgrading is really related only these renamings,
it might be less stressful just to create function aleases, like:

create function pg_catalog.pg_last_xlog_receive_location() returns pg_lsn
as $$
  select pg_current_wal_lsn();
$$ language sql;


+ granting proper permissions to specified DB roles, where needed.

Of course, it's hacky and should be only as a temporary solution until the
app is adjusted to support Postgres 10+.
But it gives an ability to have all good things added to Postgres 9.6 and
10.

Is my text_pattern_ops index working for a LIKE prefix search with column reference?

[Seamus Abshere]

hi,

I created an index with text_pattern_ops because I want fast prefix search [1] 
[2].

CREATE INDEX ON mytable USING BTREE (state, city, address text_pattern_ops);

(Note it's composite - I'm looking for prefixes on the address part only.) I 
can see that it "works" (purely uses the index) for prefix searches without 
column references:

# explain select 1 from mytable where state = 'x' and city = 'x' and address 
like 'asd%';
QUERY PLAN
--
 Index Only Scan using mytable_state_city_house_number_and_s_idx1 on mytable  
(cost=0.41..4.44 rows=1 width=4)
   Index Cond: ((state = 'x'::text) AND (city = 'x'::text) AND (address ~>=~ 
'asd'::text) AND (address ~<~ 'ase'::text))
   Filter: (address ~~ 'asd%'::text)
(3 rows)

...but I don't think it's working when I use a column reference || '%'... I say 
that because "address" no longer appears in the "Index Cond":

# explain select (select 1 from mytable where state = 'x' and city = 'x' and 
address like generate_series.generate_series::text || '%') t2 from 
generate_series(0, 10);
QUERY PLAN
--
 Function Scan on generate_series  (cost=0.00..4455.00 rows=1000 width=4)
   SubPlan 1
 ->  Index Only Scan using mytable_state_city_house_number_and_s_idx1 on 
mytable  (cost=0.41..4.44 rows=1 width=4)
   Index Cond: ((state = 'x'::text) AND (city = 'x'::text))
   Filter: (address ~~ ((generate_series.generate_series)::text || 
'%'::text))
(5 rows)

Any advice? I'm on 10.3. Also, has this changed between 9.6 and 10.3?

And... maybe I'm asking the wrong question, how can I do a fast search on 
state, city, address prefix?

Thanks,
Seamus

[1] http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops.html
[2] https://blog.2ndquadrant.com/text-search-strategies-in-postgresql/

Join condition parsing puzzle

[Mark Jeffcoat]

I'm looking at a query generated by SQLAlchemy. It works; Postgres is
perfectly happy to run it, and it gives answers that make sense to the
guy who wrote it. But I don't understand why it works.

Stripped way down ...

CREATE VIEW relation_a (id_c, id_v)
AS VALUES (1, 20),  (2, 21), (3, 22);

CREATE VIEW relation_b (id_c, id_v, id_p)
AS VALUES (1, 20, 300), (2, 21, 301);

CREATE VIEW relation_c (id_p)
AS VALUES (301);

SELECT *
FROM relation_a
LEFT JOIN relation_b
JOIN relation_c
ON (relation_c.id_p = relation_b.id_p)
ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = relation_b.id_v);


I would have claimed before seeing this example that it wasn't even
grammatical; I thought the only legal place to write the ON clause was
immediately after the JOIN. Apparently not.

How should I read this query?  I'd appreciate any help understanding this.

-- 
Mark Jeffcoat
Austin, TX

Re: Join condition parsing puzzle

[Tom Lane]

Mark Jeffcoat  writes:
> SELECT *
> FROM relation_a
> LEFT JOIN relation_b
> JOIN relation_c
> ON (relation_c.id_p = relation_b.id_p)
> ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = relation_b.id_v);

> I would have claimed before seeing this example that it wasn't even
> grammatical; I thought the only legal place to write the ON clause was
> immediately after the JOIN. Apparently not.

> How should I read this query?  I'd appreciate any help understanding this.

You read it as

SELECT *
FROM
  relation_a
  LEFT JOIN (relation_b
 JOIN relation_c
 ON (relation_c.id_p = relation_b.id_p))
  ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = relation_b.id_v);

There's no other valid way to parenthesize it, so that's what
the parser does.

regards, tom lane

Re: Is my text_pattern_ops index working for a LIKE prefix search with column reference?

[Laurenz Albe]

Seamus Abshere wrote:
> I created an index with text_pattern_ops because I want fast prefix search 
> [1] [2].
> 
> ...but I don't think it's working when I use a column reference || '%'...

An index can only be used for expressions like

 

and you have a column refrence on the right side.

Perhaps you can rephrase your query in such a fashion.

Yours,
Laurenz Albe

[no subject]

[Raghavendra Rao J S V]

Hi All,

In order to implment connection pool mechanisam, we have decided to use
pg_bouncer. But due to some dependent software's like openssl we are not
going to use pg_bouncer.

Therefore we are planning to use pgpool to implement the connection pool
mechanisam in my applicaiton.

We don't have master and stand by servers.We have only one server.

Is it advisable to use the pgpool only as connection pool mechanisam
without using loadbalancing,replication etc...?

Which are the settings need to be modified inorder to implement only
connection pool mechanisam in pgpool?

Possilbe suggest me the steps / provide me a URL to implement?

-- 
Regards,
Raghavendra Rao J S V

Which are the settings need to be modified inorder to implement only connection pool mechanisam in pgpool

[Raghavendra Rao J S V]

Hi All,

In order to implment connection pool mechanisam, we have decided to use
pg_bouncer. But due to some dependent software's like openssl we are not
going to use pg_bouncer.

Therefore we are planning to use pgpool to implement the connection pool
mechanisam in my applicaiton.

We don't have master and stand by servers.We have only one server.

Is it advisable to use the pgpool only as connection pool mechanisam
without using loadbalancing,replication etc...?

Which are the settings need to be modified inorder to implement only
connection pool mechanisam in pgpool?

Possilbe suggest me the steps / provide me a URL to implement?

-- 
Regards,
Raghavendra Rao J S V

Re: Is my text_pattern_ops index working for a LIKE prefix search with column reference?

[Tom Lane]

Laurenz Albe  writes:
> Seamus Abshere wrote:
>> I created an index with text_pattern_ops because I want fast prefix search 
>> [1] [2].
>> ...but I don't think it's working when I use a column reference || '%'...

> An index can only be used for expressions like
>  
> and you have a column refrence on the right side.

"constant" is a bit too strong here, but it definitely can't be
"expression using another column from the same table".  It has to
be an expression that will hold stable throughout a scan of the
table you wish to index.

regards, tom lane

Re: Upgrade/Downgrade

[Achilleas Mantzios]

On 23/08/2018 22:40, Nikolay Samokhvalov wrote:

On Thu, Aug 23, 2018 at 11:44 AM Joshua D. Drake mailto:j...@commandprompt.com>> wrote:

On 08/23/2018 04:47 AM, Achilleas Mantzios wrote:
> On 23/08/2018 14:30, Sonam Sharma wrote:
>> No, I didn't. The version is not compatible with application, so need
>> to downgrade it
>
> Have you tried to tweak postgresql.conf ?
> What do you mean is not compatible? What's the problem?

My guess would be that they have a vendor supported application that is
*only* supported on 9.5. I run into this more often than I like.


Same story.

I suppose it's related (at least partially) xlog->wal and **_location->**_lsn renamings in function names (full list made in Postgres 10: 
https://wiki.postgresql.org/wiki/New_in_postgres_10#Renaming_of_.22xlog.22_to_.22wal.22_Globally_.28and_location.2Flsn.29)

Those 10's changes are really painful.

I wish there were be some grace period, say 2-3 years, when both pg*_xlog_* 
function names would also work as aliases for pg*_wal_**.

yes this is true for scripts, monitoring, but for app? I doubt many apps depend 
on wal/lsn related functions.


BTW, if the reason of downgrading is really related only these renamings, it 
might be less stressful just to create function aleases, like:

create function pg_catalog.pg_last_xlog_receive_location() returns pg_lsn 
as $$
  select pg_current_wal_lsn();
$$ language sql;


+ granting proper permissions to specified DB roles, where needed.

Of course, it's hacky and should be only as a temporary solution until the app 
is adjusted to support Postgres 10+.
But it gives an ability to have all good things added to Postgres 9.6 and 10.



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt