> Paul Jones hat am 18. März 2016 um 21:24 geschrieben:
>
>
> In Postgres 9.5.1 with a shared_buffer cache of 7Gb, a SELECT from
> a single table that uses an index appears to read the table into the
> shared_buffer cache. Then, as many times as the exact same SELECT is
> repeated in the same
Hi,
Thanks for your prompt response.
Appreciate your help.
Thanks and best regards,
bocap
> Hi
>
> 2016-03-16 15:58 GMT+01:00 Dang Minh Huong :
>> Hi,
>>
>> Why does log temp files are created twice when query is executed in PL/pgSQL
>> function?
>> Would you please explain it to me?
>
> PL/
Hi List,
I am doing a INSERT...ON CONFLICT...UPDATE.. on a table. The query is
mostly-UPDATE and does not change any columns most of the time, like
so:
CREATE INDEX ON book(title);
INSERT INTO book (isbn, title, author, lastupdate) VALUES ($1,$2,$3, now())
ON CONFLICT (isbn) DO UPDATE set
Guys
Whats the best way to grant select on pg_stat_activity so that non
super user can access this view.
Thanks
Avi
On 3/17/16 5:07 PM, David G. Johnston wrote:
> Figured out it had to be added to 2016-09...done
Hmm ... this patch is currently marked "needs review" in CF 2016-03. Am
I missing something, should this have been closed?
--
-David
da...@pgmasters.net
--
Sent via pgsql-general mailing list (pg
On 03/18/2016 02:55 AM, Leonardo M. Ramé wrote:
Hi, I have read and re-read the Partitioning chapter
(http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html), but I
still don't see how to implement this use case:
One table storing current data, let's call it the "master table", then
one
On Wed, Mar 16, 2016 at 1:59 PM, drum.lu...@gmail.com
wrote:
>
> 1 - The problem here is that a VACUUM FULL will lock all the DB to wirte,
> am I right? My DB is 1.7 TB, so it will take a while and the System can't
> be offline
>
>1. Migrate the files to the NFS server
>2. Delete the sche
On Wed, Mar 16, 2016 at 2:27 PM, drum.lu...@gmail.com
wrote:
>
>
> On 17 March 2016 at 10:21, David G. Johnston
> wrote:
>
>> On Wed, Mar 16, 2016 at 1:59 PM, drum.lu...@gmail.com <
>> drum.lu...@gmail.com> wrote:
>>
>>>
>>> 1 - The problem here is that a VACUUM FULL will lock all the DB to
>>>
On 18 March 2016 at 03:23, Adrian Klaver wrote:
> On 03/16/2016 07:07 PM, drum.lu...@gmail.com wrote:
>
>>
>>
>>
>>
>> I see a lot of other problems: you have 3 independet tables. Your 2
>> queries
>> (selects) returns 2 independet results, you can't use that for
>> insert into th
Maybe my custom settings are relevant. Here they are in a gist:
https://gist.github.com/skehlet/08aeed3d06f1c35bc780
On Thu, Mar 17, 2016 at 11:47 AM Steve Kehlet
wrote:
> Sorry, seems like such a noob problem, but I'm stumped. This is postgres
> 9.4.5. I'll post my custom settings if desired b
On 03/16/2016 02:40 PM, otheus uibk wrote:
On Wednesday, March 16, 2016, Thomas Munro
Somehow, the documentation misleads (me) to believe the async
replication algorithm at least guarantees WAL records are *sent* before
responding "committed" to the client. I now know this is not the case.
*g
Michael Paquier writes:
> On Fri, Mar 18, 2016 at 9:00 AM, Tom Lane wrote:
>> Hmm ... I can't find the string "insufficient data in the message"
>> anywhere in the Postgres sources. And not "pgsql_pexec" either.
>> 2016-03-16 17:35:07 BRT ERRO: dados insuficientes na mensagem
> This is an err
On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum.lu...@gmail.com wrote:
> Can you please provide me a Query that tells me how much space is a Schema
> in my DB?
There's been a discussion on that recently (like last month)
which can be found in the archive.
Karsten
--
GPG key ID E4071346 @ eu.pool
Hi, I around with error with PostgreSQL 9.5.0
Prepared Statment:INSERT INTO tbCerts (Company_UID, User_UID, Cert_Blob_Size,
Cert_Format, After_Date, Before_Date, Cert_Blob, Password1, Key_Name,
Cert_Blob_Type, Cert_Use, Status) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9,
$10, $11, $12);
Binds va
On Fri, Mar 18, 2016 at 9:31 AM, Aleksander Alekseev <
a.aleks...@postgrespro.ru> wrote:
> You can use schema name as a prefix:
>
> postgres=# \d+
>List of relations
> Schema | Name | Type | Owner |Size| Description
> ---+--+---+--+--
Hi,
Why does log temp files are created twice when query is executed in PL/pgSQL
function?
Would you please explain it to me?
As below test result. Log temp files are created twice when SELECT statement is
put
into a PL/pgSQL function. It led a little of performance degradation.
Is there any
On 17 March 2016 at 10:21, David G. Johnston
wrote:
> On Wed, Mar 16, 2016 at 1:59 PM, drum.lu...@gmail.com <
> drum.lu...@gmail.com> wrote:
>
>>
>> 1 - The problem here is that a VACUUM FULL will lock all the DB to wirte,
>> am I right? My DB is 1.7 TB, so it will take a while and the System can
Thomas Kellerer writes:
> 2016-03-12 13:51:29.305 CET [23912]: [2-1] user=arthur,db=prod,app=[unknown]
> DETAIL: Process 23912 waits for ShareLock on transaction; blocked by process
> 24342.
> Process 24342 waits for ShareLock on transaction 39632974; blocked by
> process 23912.
>
On 03/18/2016 01:09 PM, avi Singh wrote:
Guys
Whats the best way to grant select on pg_stat_activity so that
non super user can access this view.
They should be able to, see below. If that is not your case, then more
information is needed.
guest@test=> select current_user;
current_
Just to throw some extreme ideas out there, you could stand up a postgres
on some other server, pg_dump your current database and use that dump to
build up your second postgres. Use that new postgres when your system goes
live again after downtime. Restoring from a dump means your database would
no
*Error*: psycopg2.OperationalError: could not translate host name
"localhost" to address: Name or service not known
*Solution*: Sometimes this error come just because that your LAN cable is
not connect to your system.
To resolve this problem connect your LAN cable to system then again run the
co
Tom Lane schrieb am 16.03.2016 um 14:45:
>> 2016-03-12 13:51:29.305 CET [23912]: [2-1] user=arthur,db=prod,app=[unknown]
>> DETAIL: Process 23912 waits for ShareLock on transaction; blocked by process
>> 24342.
>> Process 24342 waits for ShareLock on transaction 39632974; blocked
>> by
>
>
>
> I see a lot of other problems: you have 3 independet tables. Your 2 queries
> (selects) returns 2 independet results, you can't use that for insert into
> the
> 3rd table. And i think, you are looking for an update, not insert. So you
> have
> to define how your tables are linked together (
On Thu, Mar 17, 2016 at 7:27 AM, Mike Blackwell
wrote:
> I have a large table with numerous indexes which has approximately doubled
> in size after adding a column - every row was rewritten and 50% of the
> tuples are dead. I'd like to reclaim this space, but VACUUM FULL cannot
> seem to finish
On Thu, Mar 17, 2016 at 4:45 PM, Karsten Hilbert
wrote:
> On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum.lu...@gmail.com wrote:
>
> > Can you please provide me a Query that tells me how much space is a
> Schema
> > in my DB?
>
> There's been a discussion on that recently (like last month)
> which
Albe Laurenz schrieb am 16.03.2016 um 13:20:
>> The error as reported in the Postgres log file is this:
>>
>> 2016-03-12 13:51:29.305 CET [23912]: [1-1] user=arthur,db=prod,app=[unknown]
>> ERROR: deadlock detected
>> 2016-03-12 13:51:29.305 CET [23912]: [2-1] user=arthur,db=prod,app=[unknown]
>>
I am attempting to setup a small postgresql database on a beaglebone black. The application is slow so I am storing the database on a 64GB microsd card. I formatted the drive, mounted it all correctly.I logged in as postgressudo su postgres# create the database cluster./initdb /data # the mou
On Fri, Mar 18, 2016 at 7:08 AM, Sándor Daku wrote:
>
> On 18 March 2016 at 10:55, Leonardo M. Ramé wrote:
>
>> Hi, I have read and re-read the Partitioning chapter (
>> http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html), but I
>> still don't see how to implement this use case:
>>
On Fri, Mar 18, 2016 at 9:00 AM, Tom Lane wrote:
> Ranier VF writes:
>> Hi, I around with error with PostgreSQL 9.5.0
>> Error: insufficient data in the messagepgsql_pexec error: PQresultStatus=7
>
> Hmm ... I can't find the string "insufficient data in the message"
> anywhere in the Postgres so
On 3/17/16 7:00 PM, Tom Lane wrote:
> David Steele writes:
>> On 3/17/16 5:07 PM, David G. Johnston wrote:
>>> Figured out it had to be added to 2016-09...done
>
>> Hmm ... this patch is currently marked "needs review" in CF 2016-03. Am
>> I missing something, should this have been closed?
>
>
On 03/18/2016 08:49 AM, Ranier VF wrote:
Ccing list for real this time, for reasons stated below.
Hi,
> Per previous posts, the error is not coming from Postgres, but from your
> client software. Some searching indicates pgsql_pexec is part of the
> Asterisk PBX software. Is that the case?
I should have noted: the application is using PostgreSQL 9.5.0, running on
Ubuntu 14.04
Mike
On Fri, Mar 18, 2016 at 10:41 AM, Michael Charnoky wrote:
> Hi, I'm seeing random errors from an application that is performing DB
> inserts. The error happens spuriously and looks like this from the
>
On 15 March 2016 at 05:17, Dustin Kempter
wrote:
> However my instances are not on the same server and I attempted to simply
> add a host=(the ip) but that failed. Please help
>
Review the logs on both hosts to see any errors during setup.
Note that you will need to drop and re-create the datab
Hello,
I had an issue with the behavior of pg_basebackup command. I was convinced
previously that pg_basebackup command always made the binary copy of the
database cluster files of the postgres master. However, I recently noticed that
pg_basebackup did not copy the the replication slot object
On Thu, Mar 10, 2016 at 1:40 AM, David G. Johnston
wrote:
> Adding -hackers for consideration in the Commitfest.
I don't much like how this patch uses the arbitrary constant 50 in no
fewer than 5 locations.
Also, it seems like we could arrange for head_title to be "" rather
than NULL when myopt.
Hello,
On 19/03/2016 15:41, Yi, Yi wrote:
> Hello,
>
> I had an issue with the behavior of pg_basebackup command. I was convinced
> previously that pg_basebackup command always made the binary copy of the
> database cluster files of the postgres master. However, I recently noticed
> that pg_b
On 19/03/2016 15:58, Julien Rouhaud wrote:
> Hello,
>
> On 19/03/2016 15:41, Yi, Yi wrote:
>> Hello,
>>
>> I had an issue with the behavior of pg_basebackup command. I was convinced
>> previously that pg_basebackup command always made the binary copy of the
>> database cluster files of the post
On 03/17/2016 08:20 AM, ray madigans.org wrote:
I am attempting to setup a small postgresql database on a beaglebone
black. The application is slow so I am storing the database on a 64GB
microsd card. I formatted the drive, mounted it all correctly.
I logged in as postgres
sudo su postgres
#
Hi, I'm seeing random errors from an application that is performing DB
inserts. The error happens spuriously and looks like this from the
application side:
could not open file "/dev/shm/postgres_apptable_47861701461760" for
reading: No such file or directory
The PostgreSQL logs show:
2016-03-18
On Friday, March 18, 2016 1:18:01 PM EDT ando...@aule.net wrote:
> Hi,
>
> After upgrading to PostgreSQL 9.4.6, our test system gave error messages
> like:
>
> ERROR: invalid salt
>
> The cause of these errors is statements like:
>
> WHERE password = crypt('secret', 'secret')
>
> After
Michael Charnoky writes:
> Hi, I'm seeing random errors from an application that is performing DB
> inserts. The error happens spuriously and looks like this from the
> application side:
> could not open file "/dev/shm/postgres_apptable_47861701461760" for
> reading: No such file or directory
Of
>
>>
>>
> I don't deal with Hot Standby's in my day-to-day but if you DROP SCHEMA
> all of the spaced consumed by indexes and tables in that schema will be
> freed. The vacuum might make a small difference in performance on the
> system catalogs (pg_class, stats, etc) that were updated but with
On 03/16/2016 01:49 PM, Ranier VF wrote:
Hi, I around with error with PostgreSQL 9.5.0
Prepared Statment:
INSERT INTO tbCerts (Company_UID, User_UID, Cert_Blob_Size, Cert_Format,
After_Date, Before_Date,
Cert_Blob, Password1, Key_Name, Cert_Blob_Type, Cert_Use, Status)
VALUES ($1, $2, $3, $4, $5
Thomas Kellerer wrote:
>>> The error as reported in the Postgres log file is this:
>>>
>>> 2016-03-12 13:51:29.305 CET [23912]: [1-1]
>>> user=arthur,db=prod,app=[unknown] ERROR: deadlock detected
>>> 2016-03-12 13:51:29.305 CET [23912]: [2-1]
>>> user=arthur,db=prod,app=[unknown] DETAIL: Process
On 3/14/2016 2:43 PM, Roland van Laar wrote:
However my instances are not on the same server and I attempted to
simply add a host=(the ip) but that failed.
There are a couple of other factors:
- is postgres running on an external available ip?
- is there a replication user with a password?
3:
I have a large table with numerous indexes which has approximately doubled
in size after adding a column - every row was rewritten and 50% of the
tuples are dead. I'd like to reclaim this space, but VACUUM FULL cannot
seem to finish within the scheduled downtime.
Any suggestions for reclaiming th
On Wednesday, March 16, 2016, Thomas Munro
wrote:
> In asynchronous replication, the primary writes to the WAL and flushes
the disk. Then, for any standbys that happen to be connected, a WAL sender
process trundles along behind feeding new WAL doesn the socket as soon as
it can, but it can be ru
On Wed, Mar 16, 2016 at 6:49 PM, Andreas Kretschmer wrote:
>
>
> > "drum.lu...@gmail.com" hat am 17. März 2016 um
> 02:34
> > geschrieben:
> >
> >
> > I'm trying to insert data from TABLE A to TABLE B.
> >
> > 1 - Select billable_id from dm.billable
> > 2 - Select mobiuser_id from ja_mobiusers
>
Hi, I have read and re-read the Partitioning chapter
(http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html), but I
still don't see how to implement this use case:
One table storing current data, let's call it the "master table", then
one or more partitions with old data.
For examp
On Thu, Mar 17, 2016 at 10:57 AM, bricklen wrote:
> On Thu, Mar 17, 2016 at 7:27 AM, Mike Blackwell
> wrote:
>
>> I have a large table with numerous indexes which has approximately
>> doubled in size after adding a column - every row was rewritten and 50% of
>> the tuples are dead. I'd like to
Hi
2016-03-16 15:58 GMT+01:00 Dang Minh Huong :
> Hi,
>
> Why does log temp files are created twice when query is executed in
> PL/pgSQL function?
> Would you please explain it to me?
>
PL/pgSQL materialize result internally.
Usually PostgreSQL operations are executed row by row. But some SRF
f
> >
> Hi Andreas!
>
> Well...
>
> There are two tables that I need to get data from(dm.billables /
> public.ja_mobiusers), and a third table (dm.billables_links) that I need to
> insert data from those two tables.
lets start from here. you have 2 tables:
test=*# select * from source1;
i
---
>
>
> assuming those two queries 1 and 2 return multiple rows, which rows of
> junk.wm_260_billables2 match up with what rows of public.ja_mobiusers ?
>
> your schema is very poorly defined. I think you need to take a class in
> relational database design and usage, or read a good book on
On 03/15/2016 08:10 AM, Matthias Schmitt wrote:
Hello,
since two weeks I am trying to get PostgreSQL 9.5.1 to run on Debian 8.3.
Everything is fine except the daily backup. When calling pg_dump as part of a
cron job pg_dump crashes:
2016-03-15 01:00:02 CETFATAL: semctl(23232524, 3, SETVAL, 0
> "drum.lu...@gmail.com" hat am 17. März 2016 um 02:34
> geschrieben:
>
>
> I'm trying to insert data from TABLE A to TABLE B.
>
> 1 - Select billable_id from dm.billable
> 2 - Select mobiuser_id from ja_mobiusers
> 3 - Insert the billable_id and the mobiuser_id to the dm.billables_links
> ta
On Tuesday, March 15, 2016 7:39 PM, "p...@cmicdo.com"
wrote:
> Your results are close enough to mine, I think, to prove the point.
> And, I agree that the EDB benchmark is not necessary reflective of a
> real-world scenario.
>
> However, the cache I'm referring to is PG's shared_bu
is there a possibility that there is no index on the FKY column
bravo.alpha_id.
On Wed, Mar 16, 2016 at 11:09 AM, Albe Laurenz
wrote:
> Thomas Kellerer wrote:
> >> Can you determine what statements were executed in these transactions
> before the deadlock?
> >> It was probably one of these that
On Wed, Mar 16, 2016 at 10:40:03PM +0100, otheus uibk wrote:
> Somehow, the documentation misleads (me) to believe the async replication
> algorithm at least guarantees WAL records are *sent* before responding
> "committed" to the client. I now know this is not the case. *grumble*.
>
> How can I h
Daniel,
* CHENG Yuk-Pong, Daniel (j16s...@gmail.com) wrote:
> I am doing a INSERT...ON CONFLICT...UPDATE.. on a table. The query is
> mostly-UPDATE and does not change any columns most of the time, like
> so:
>
> CREATE INDEX ON book(title);
> INSERT INTO book (isbn, title, author, lastupdat
On Friday, March 18, 2016 4:54 PM, Andreas Kretschmer
wrote:
>
>
>> Paul Jones hat am 18. Marz 2016 um 21:24 geschrieben:
>>
>>
>> In Postgres 9.5.1 with a shared_buffer cache of 7Gb, a SELECT from
>
> the first query reads only the tuple from heap that are matched the
> where-condit
On Friday, March 18, 2016, Durumdara wrote:
>
> I want to avoid to remove any real table on resource closing (= dropping
> of temporary table).
> How to I force "drop only temporary"? Prefix, option, etc.
>
>
If you have to explicitly drop a temporary table you are likely doing
something wrong.
Hi,
After upgrading to PostgreSQL 9.4.6, our test system gave error messages
like:
ERROR: invalid salt
The cause of these errors is statements like:
WHERE password = crypt('secret', 'secret')
After reverting to Postgres 9.4.4 the test system worked properly again.
This might be relate
On Mon, Mar 14, 2016 at 1:20 PM, Peter Geoghegan wrote:
> On Mon, Mar 14, 2016 at 12:28 PM, Peter Devoy wrote:
>> Is there a reason DO NOTHING was not developed for use with RETURNING?
>
> I don't know what you mean. It should work fine with RETURNING.
He wants to retrieve a value from the conf
Autovacuum will eventually free your extra pages regarding index bloat but
it takes multiple runs.
You could also use reindex instead of vacuum full since you are only
interested in the index.
For the table there may be other options but they depend on your pattern of
writes.
On Thu, Mar 17, 201
Figured out it had to be added to 2016-09...done
On Wed, Mar 9, 2016 at 11:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> Adding -hackers for consideration in the Commitfest.
>
> Thanks!
>
> David J.
>
> >>>Original request by me
>
>
> http://www.postgresql.org/message-id/CAKFQuw
Ranier VF writes:
> Hi, I around with error with PostgreSQL 9.5.0
> Error: insufficient data in the messagepgsql_pexec error: PQresultStatus=7
Hmm ... I can't find the string "insufficient data in the message"
anywhere in the Postgres sources. And not "pgsql_pexec" either.
So this must be comin
On Fri, Mar 18, 2016 at 8:16 AM, Tom Lane wrote:
> David Steele writes:
>> On 3/17/16 7:00 PM, Tom Lane wrote:
>>> The message I saw was post-1-March. If it was in fact submitted in
>>> time for 2016-03, then we owe it a review.
>
>> I meant to add the CF record and forgot:
>> https://commitfest
On 03/16/2016 07:58 AM, Dang Minh Huong wrote:
Hi,
Why does log temp files are created twice when query is executed in PL/pgSQL
function?
Would you please explain it to me?
As below test result. Log temp files are created twice when SELECT statement is
put
into a PL/pgSQL function. It led
On Wed, Mar 16, 2016 at 9:34 PM, drum.lu...@gmail.com
wrote:
> The problem is that I need to do that at the same time, because of a
> constraint:
>
>
Mark your constraint as deferrable, and then defer the constraints within
your transaction.
69 matches
Mail list logo
