log level of "drop cascade" lists
Hi, (this is in version 9.4) The SQL command DROP schema myschema CASCADE tells me that the full list of items that the drop cascades to is in the log, but it isn't. messages on stdout: ... drop cascades to table myschema.mytable and 143 other objects (see server log for list) DROP SCHEMA The log doesn't mention this at all, except 2019-01-10 12:10:45 CET ERROR: canceling autovacuum task 2019-01-10 12:10:45 CET CONTEXT: automatic analyze of table "myschema.mytable" log_min_messages is on the default value, which is warning. #log_min_messages = warning At first glance, it seems logical that the list of dropped items is a "notice". But now that it seems that the cascade went further than i anticipated, it is of a greater significance to me than that. Also, truncating the list in the message and referring to the log is not desirable IMHO if the default setting is to not log the list. So long story short: i think it would be wise to set the log level of "drop cascade" lists to "warning". Cheers, -- Willy-Bas Loos
Lost synchronization with server: got message type"0" , length 879046704
Hi, Some times, I am getting below error when we perform DB update in parallel with select operation. *Lost synchronization with server: got message type"0" , length 879046704* Is it a bug?. Please help to resolve. Postgre server version: 10 Libpq version is 9.2.3 Regards, Arun Menon
Postgres wont remove useless joins, when the UNIQUE index is partial
Hi
remove_useless_join does not prove uniqueness if the unique index is partial,
and therefore wont remove the join if no columns are referenced (see example in
bottom).
I have been trying to look around the source code and from what I have
identified the problem seems to be that "check_index_predicates(..)" happens
after "remove_useless_join(..)", and therefore cannot see that the unique index
is actually covered by the join condition.
>From analyzejoins.c:612, rel_supports_distinctness(..)
if (ind->unique && ind->immediate &&
(ind->indpred == NIL || ind->predOK))
return true;
But the problem is ind->predOK is calculated in check_index_predicates(..) but
this happens later so ind->predOK is always false when checked here.
I have tried to add check_index_predicates(..) to rel_supports_distinctness(..)
and this produces the expected plan, but I have no idea of the implication of
doing check_index_predicates(..) earlier.
This is my first time looking at the postgres source code, so I know attached
"patch" is not the solution, but any pointers on where to go from here would be
appreciated.
Example:
CREATE TABLE a (
id INTEGER PRIMARY KEY,
sub_id INTEGER NOT NULL,
deleted_at TIMESTAMP
);
CREATE UNIQUE INDEX ON a (sub_id) WHERE (deleted_at IS NULL);
ANALYZE a;
EXPLAIN SELECT 1 FROM a AS a LEFT JOIN a AS b ON a.id = b.sub_id AND
b.deleted_at IS NULL;
Expected plan:
QUERY PLAN
-
Seq Scan on a (cost=0.00..28.50 rows=1850 width=4)
Actual plan:
QUERY PLAN
---
Hash Left Join (cost=14.76..48.13 rows=1850 width=4)
Hash Cond: (a.id = b.sub_id)
-> Seq Scan on a (cost=0.00..28.50 rows=1850 width=4)
-> Hash (cost=14.65..14.65 rows=9 width=4)
-> Bitmap Heap Scan on a b (cost=4.13..14.65 rows=9 width=4)
Recheck Cond: (deleted_at IS NULL)
-> Bitmap Index Scan on a_sub_id_idx (cost=0.00..4.13 rows=9
width=0)
(7 rows)
mvh
Kim Carlsen
Hiper A/S
M: 71 99 42 00
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 1593dbec21..12da689983 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -596,6 +596,7 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
return false;
if (rel->rtekind == RTE_RELATION)
{
+ check_index_predicates(root, rel);
/*
* For a plain relation, we only know how to prove uniqueness by
* reference to unique indexes. Make sure there's at least one
Re: Pulling data from Postgres DB table for every 5 seconds.
I am not familiar with Aurora, but.. What something like https://github.com/subzerocloud/pg-amqp-bridge? Set up a message queue in Postgres, which calls into AMPQ (RabbitMQ) to send a message for consumption by one or more clients. This provides a function that can be called from a trigger to send the message. After that, you have all the goodness of a standards-based open source MQ platform to distribute your data / notifications. On Wed, 9 Jan 2019 at 19:02, github kran wrote: > >> Hi Postgres Team, >> >> I have an application using RDS Aurora Postgresql 9.6 version having 4 TB >> of DB size. In this DB we have a table PRODUCT_INFO with around 1 million >> rows and table size of 1 GB. >> We are looking for a implementation where we want to pull the data in >> real time for every 5 seconds from the DB ( Table mentioned above) and send >> it to IOT topic whenever an event occurs for a product. ( event is any new >> product information or change in the existing >> product information.). >> >> This table has few DML operations in real time either INSERT or UPDATE >> based on the productId. ( Update whenever there is a change in the product >> information and INSERT when a record doesnt exists for that product). >> >> We have REST API's built in the backend pulling data from this backend >> RDS Aurora POSTGRES DB and used by clients. >> >> *UseCase* >> We dont want clients to pull the data for every 5 seconds from DB but >> rather provide a service which can fetch the data from DB in real time and >> push the data to IOT topic by pulling data for every 5 seconds from DB. >> >> *Questions* >> 1) How can I get information by pulling from the DB every 5 seconds >> without impacting the performance of the DB. >> 2) What are the options I have pulling the data from this table every 5 >> seconds. Does POSTGRES has any other options apart from TRIGGER ?. >> >> >> Any ideas would be helpful. >> >> Thanks !! >> GithubKran >> >
Re: jdbc PGCopyOutputStream close() v. endCopy()
Hi Rob,
Interesting. I've not looked too much into the copy implementation.
The JDBC list or the jdbc github repo https://github.com/pgjdbc/pgjdbc
might be a better place to report this. I know Lukas Edar monitors it as
well
Dave Cramer
da...@postgresintl.com
www.postgresintl.com
On Tue, 8 Jan 2019 at 16:29, Rob Sargent wrote:
> As is often the case, I'm unsure of which of these methods to use, or if
> I'm using them correctly.
>
> PG10.5, jooq-3.10.8, postgresql-42.1.4, linux (redhat 6.9) and logback to
> a file.
>
> I have been using close() for a while but thought I would make use of
> either the returned long from endCopy() or perhaps getHandledRowCount().
>
> Both work perfectly, but when I use endCopy() I always get the exception
> shown near the bottom of this log excerpt. The COPY is on its own thread
> from the same connection as the direct jooq writes also listed. Again, the
> data is all saved but I am worried that I'm not closing properly even if I
> use close(). The data here doesn't warrent bulk copy but it's just a quick
> example to repeat.
>
> 13:32:55.785 [pool-1-thread-1] DEBUG edu.utah.camplab.jx.PayloadFromMux -
> STAGING TABLE CREATED: bulk."flk_g16-forcing very long name to trigger
> truncation_22_8045c0"
> 13:32:55.786 [pool-1-thread-1] INFO edu.utah.camplab.jx.PayloadFromMux -
> 8045c057-99ec-490b-90c1-85875269afee: started COPY work at 1546979575786
> 13:32:55.789 [pool-1-thread-1] INFO edu.utah.camplab.jx.PayloadFromMux -
> 8045c057-99ec-490b-90c1-85875269afee: Total segment save took 22 ms
> 13:32:55.790 [pool-1-thread-1] INFO edu.utah.camplab.jx.AbstractPayload -
> 8045c057-99ec-490b-90c1-85875269afee: closing process
> 8045c057-99ec-490b-90c1-85875269afee
> 13:32:55.790 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulk."flk_g16-forcing very long name
> to trigger truncation_22_8045c0": Begin bulk copy segment
> 13:32:55.793 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulked up to 89, maybe?
> 13:32:55.793 [pool-1-thread-1] DEBUG org.jooq.tools.LoggerListener -
> Executing batch query: insert into "process_input" ("id", "process_id",
> "input_type", "input_ref") values (?, ?, ?, ?)
> 13:32:55.795 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulked up to 197, maybe?
> 13:32:55.797 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulked up to 318, maybe?
> 13:32:55.798 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - bulked up to 393, maybe?
> 13:32:55.799 [8045c057-99ec-490b-90c1-85875269afee] INFO
> e.u.camplab.jx.PayloadWriterThread - 393/393 segments delivered in 9 ms
> 13:32:55.799 [8045c057-99ec-490b-90c1-85875269afee] DEBUG
> e.u.camplab.jx.PayloadWriterThread - staged in 9 ms
> 13:32:55.800 [pool-1-thread-1] DEBUG org.jooq.tools.LoggerListener -
> Executing batch query: insert into "process_output" ("id",
> "process_id", "output_type", "output_ref") values (?, ?, ?, ?)
> 13:32:55.805 [8045c057-99ec-490b-90c1-85875269afee] ERROR
> e.u.camplab.jx.PayloadWriterThread - bulk."flk_g16-forcing very long name
> to trigger truncation_22_8045c0": i/o trouble
> java.io.IOException: Ending write to copy failed.
> at
> org.postgresql.copy.PGCopyOutputStream.close(PGCopyOutputStream.java:107)
> ~[postgresql-42.1.4.jar:42.1.4]
> at
> edu.utah.camplab.jx.PayloadWriterThread.run(PayloadWriterThread.java:75)
> ~[transport/:na]
> Caused by: org.postgresql.util.PSQLException: Tried to write to an
> inactive copy operation
> at
> org.postgresql.core.v3.QueryExecutorImpl.writeToCopy(QueryExecutorImpl.java:978)
> ~[postgresql-42.1.4.jar:42.1.4]
> at org.postgresql.core.v3.CopyInImpl.writeToCopy(CopyInImpl.java:35)
> ~[postgresql-42.1.4.jar:42.1.4]
> at
> org.postgresql.copy.PGCopyOutputStream.endCopy(PGCopyOutputStream.java:166)
> ~[postgresql-42.1.4.jar:42.1.4]
> at
> org.postgresql.copy.PGCopyOutputStream.close(PGCopyOutputStream.java:105)
> ~[postgresql-42.1.4.jar:42.1.4]
> ... 1 common frames omitted
> 13:32:55.810 [pool-1-thread-1] DEBUG org.jooq.tools.LoggerListener -
> Executing batch query: insert into "process_arg" ("id", "process_id",
> "argname", "argvalue_int", "argvalue_float", "argvalue_text") values (?, ?,
> ?, ?, ?, ?)
>
> The class doing the bulk work, PayloadWriterThread extends Thread, the
> thread name is set from the caller and the critical parts are as follows:
>
> @Override
> public void run() {
> try (PGCopyOutputStream writer = new PGCopyOutputStream(copyIn)) {
> long startAt = System.currentTimeMillis();
> deliverSegments(writer);
> long postDeliver = System.currentTimeMillis();
> logger.debug("staged in {} ms", postDeliver - startAt);
> }
> catch (SQLException sqle) {
> sqle.printStackTrace();
> logger.error("{}: sql trouble", tableNam
Re: Not sure which part of the query needs optimization
Thank you for the comments, Andrew - On Mon, Jan 7, 2019 at 8:40 PM Andrew Gierth wrote: > The obvious thing to do is to keep a computed average score for each > user - either in a separate table which you update based on changes to > words_moves, which you could do with a trigger, or using a materialized > view which you refresh at suitable intervals (this has the drawback that > the data will not be immediately up-to-date). > > Combining these two changes should get you to under 100ms, maybe. > > I was considering creating a cronjob, but now I will better create a trigger as you have suggested Regards Alex
Re: jdbc PGCopyOutputStream close() v. endCopy()
Ok I’ll repost there. Thanks
> On Jan 10, 2019, at 6:09 AM, Dave Cramer wrote:
>
> Hi Rob,
>
> Interesting. I've not looked too much into the copy implementation.
> The JDBC list or the jdbc github repo https://github.com/pgjdbc/pgjdbc might
> be a better place to report this. I know Lukas Edar monitors it as well
>
> Dave Cramer
>
> da...@postgresintl.com
> www.postgresintl.com
>
>
>> On Tue, 8 Jan 2019 at 16:29, Rob Sargent wrote:
>> As is often the case, I'm unsure of which of these methods to use, or if I'm
>> using them correctly.
>>
>> PG10.5, jooq-3.10.8, postgresql-42.1.4, linux (redhat 6.9) and logback to a
>> file.
>>
>> I have been using close() for a while but thought I would make use of either
>> the returned long from endCopy() or perhaps getHandledRowCount().
>>
>> Both work perfectly, but when I use endCopy() I always get the exception
>> shown near the bottom of this log excerpt. The COPY is on its own thread
>> from the same connection as the direct jooq writes also listed. Again, the
>> data is all saved but I am worried that I'm not closing properly even if I
>> use close(). The data here doesn't warrent bulk copy but it's just a quick
>> example to repeat.
>>
>> 13:32:55.785 [pool-1-thread-1] DEBUG edu.utah.camplab.jx.PayloadFromMux -
>> STAGING TABLE CREATED: bulk."flk_g16-forcing very long name to trigger
>> truncation_22_8045c0"
>> 13:32:55.786 [pool-1-thread-1] INFO edu.utah.camplab.jx.PayloadFromMux -
>> 8045c057-99ec-490b-90c1-85875269afee: started COPY work at 1546979575786
>> 13:32:55.789 [pool-1-thread-1] INFO edu.utah.camplab.jx.PayloadFromMux -
>> 8045c057-99ec-490b-90c1-85875269afee: Total segment save took 22 ms
>> 13:32:55.790 [pool-1-thread-1] INFO edu.utah.camplab.jx.AbstractPayload -
>> 8045c057-99ec-490b-90c1-85875269afee: closing process
>> 8045c057-99ec-490b-90c1-85875269afee
>> 13:32:55.790 [8045c057-99ec-490b-90c1-85875269afee] INFO
>> e.u.camplab.jx.PayloadWriterThread - bulk."flk_g16-forcing very long name to
>> trigger truncation_22_8045c0": Begin bulk copy segment
>> 13:32:55.793 [8045c057-99ec-490b-90c1-85875269afee] INFO
>> e.u.camplab.jx.PayloadWriterThread - bulked up to 89, maybe?
>> 13:32:55.793 [pool-1-thread-1] DEBUG org.jooq.tools.LoggerListener -
>> Executing batch query: insert into "process_input" ("id", "process_id",
>> "input_type", "input_ref") values (?, ?, ?, ?)
>> 13:32:55.795 [8045c057-99ec-490b-90c1-85875269afee] INFO
>> e.u.camplab.jx.PayloadWriterThread - bulked up to 197, maybe?
>> 13:32:55.797 [8045c057-99ec-490b-90c1-85875269afee] INFO
>> e.u.camplab.jx.PayloadWriterThread - bulked up to 318, maybe?
>> 13:32:55.798 [8045c057-99ec-490b-90c1-85875269afee] INFO
>> e.u.camplab.jx.PayloadWriterThread - bulked up to 393, maybe?
>> 13:32:55.799 [8045c057-99ec-490b-90c1-85875269afee] INFO
>> e.u.camplab.jx.PayloadWriterThread - 393/393 segments delivered in 9 ms
>> 13:32:55.799 [8045c057-99ec-490b-90c1-85875269afee] DEBUG
>> e.u.camplab.jx.PayloadWriterThread - staged in 9 ms
>> 13:32:55.800 [pool-1-thread-1] DEBUG org.jooq.tools.LoggerListener -
>> Executing batch query: insert into "process_output" ("id", "process_id",
>> "output_type", "output_ref") values (?, ?, ?, ?)
>> 13:32:55.805 [8045c057-99ec-490b-90c1-85875269afee] ERROR
>> e.u.camplab.jx.PayloadWriterThread - bulk."flk_g16-forcing very long name to
>> trigger truncation_22_8045c0": i/o trouble
>> java.io.IOException: Ending write to copy failed.
>> at
>> org.postgresql.copy.PGCopyOutputStream.close(PGCopyOutputStream.java:107)
>> ~[postgresql-42.1.4.jar:42.1.4]
>> at
>> edu.utah.camplab.jx.PayloadWriterThread.run(PayloadWriterThread.java:75)
>> ~[transport/:na]
>> Caused by: org.postgresql.util.PSQLException: Tried to write to an inactive
>> copy operation
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.writeToCopy(QueryExecutorImpl.java:978)
>> ~[postgresql-42.1.4.jar:42.1.4]
>> at org.postgresql.core.v3.CopyInImpl.writeToCopy(CopyInImpl.java:35)
>> ~[postgresql-42.1.4.jar:42.1.4]
>> at
>> org.postgresql.copy.PGCopyOutputStream.endCopy(PGCopyOutputStream.java:166)
>> ~[postgresql-42.1.4.jar:42.1.4]
>> at
>> org.postgresql.copy.PGCopyOutputStream.close(PGCopyOutputStream.java:105)
>> ~[postgresql-42.1.4.jar:42.1.4]
>> ... 1 common frames omitted
>> 13:32:55.810 [pool-1-thread-1] DEBUG org.jooq.tools.LoggerListener -
>> Executing batch query: insert into "process_arg" ("id", "process_id",
>> "argname", "argvalue_int", "argvalue_float", "argvalue_text") values (?, ?,
>> ?, ?, ?, ?)
>>
>> The class doing the bulk work, PayloadWriterThread extends Thread, the
>> thread name is set from the caller and the critical parts are as follows:
>>
>> @Override
>> public void run() {
>> try (PGCopyOutputStream writer = new PGCopyOutputStream(copyIn)) {
>> long startAt = System.currentTimeMillis();
>> deliverSegments(writer);
>> long p
Re: Error on Windows
Igor Korot wrote:
> The following code compiles and executes but returns an error:
>
> [quote]
> Invalid byte sequence for encoding UTF8
> [/quote]
>
> [code]
> char *values[2];
> values[0] = NULL, values[1] = NULL;
> values[0] = new char[schemaName.length() + 1];
> values[1] = new char[tableName.length() + 1];
> memset( values[0], '\0', schemaName.length() + 1 );
> memset( values[1], '\0', tableName.length() + 1 );
> strcpy( values[0], m_pimpl->m_myconv.to_bytes( schemaName.c_str()
> ).c_str() );
> strcpy( values[1], m_pimpl->m_myconv.to_bytes( tableName.c_str()
> ).c_str() );
> int len1 = (int) schemaName.length();
> int len2 = (int) tableName.length();
> int length[2] = { len1, len2 };
> int formats[2] = { 1, 1 };
> PGresult *res = PQexecParams( m_db, m_pimpl->m_myconv.to_bytes(
> query.c_str() ).c_str(), 2, NULL, values, length, formats, 1 );
> ExecStatusType status = PQresultStatus( res );
> if( status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK )
> {
> result = 1;
> std::wstring err = m_pimpl->m_myconv.from_bytes(
> PQerrorMessage( m_db ) );
> errorMsg.push_back( L"Error executing query: " + err );
> PQclear( res );
> }
> [/code]
>
> in the "err" variable.
>
> Looking under MSVC debugger I see for a tableName a following sequence:
>
> 97 98 99 223
>
> What can I do to eliminate the error?
I'd say you should set the client encoding correctly.
Looks like the bytes represent "abcß" in some LATIN-? encoding.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: log level of "drop cascade" lists
On 1/10/19 3:28 AM, Willy-Bas Loos wrote: Hi, (this is in version 9.4) The SQL command DROP schema myschema CASCADE tells me that the full list of items that the drop cascades to is in the log, but it isn't. messages on stdout: ... drop cascades to table myschema.mytable and 143 other objects (see server log for list) DROP SCHEMA The log doesn't mention this at all, except 2019-01-10 12:10:45 CET ERROR: canceling autovacuum task 2019-01-10 12:10:45 CET CONTEXT: automatic analyze of table "myschema.mytable" log_min_messages is on the default value, which is warning. #log_min_messages = warning At first glance, it seems logical that the list of dropped items is a "notice". But now that it seems that the cascade went further than i anticipated, it is of a greater significance to me than that. Hence: 1) BEGIN; DROP schema myschema CASCADE; ROLLBACK/COMMIT; 2) \d myschema.* Also, truncating the list in the message and referring to the log is not desirable IMHO if the default setting is to not log the list. So long story short: i think it would be wise to set the log level of "drop cascade" lists to "warning". Cheers, -- Willy-Bas Loos -- Adrian Klaver adrian.kla...@aklaver.com
Re: log level of "drop cascade" lists
Willy-Bas Loos writes: > So long story short: i think it would be wise to set the log level of "drop > cascade" lists to "warning". I think that would be met with more complaints than kudos. "WARNING" is supposed to mean "there's probably something wrong here", and a report of a cascaded drop is not that. regards, tom lane
Benchmark of using JSON to transport query results in node.js
Hi! I made some benchmarks of using JSON to transport results to node.js and it seems it really makes a difference over using native or standard PostgreSQL. So the idea is that you simply wrap all results into JSON like SELECT to_json(t) FROM (... original query ...) AS t. I am guessing because node.js/JavaScript has really fast JSON parser but for everything else there is overhead. See my blog post for more details [1]. Any feedback welcome. This makes me wonder. If serialization/deserialization makes such big impact, where there efforts to improve how results are serialized for over-the-wire transmission? For example, to use something like Capnproto [2] to serialize into structure which can be directly used without any real deserialization? [1] https://mitar.tnode.com/post/181893159351/in-nodejs-always-query-in-json-from-postgresql [2] https://capnproto.org/ Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
