On 19/07/2017 11:52 PM, Tom Lane wrote:
Evan Martin <postgres...@realityexists.net> writes:
I have an application that imports a lot of data and the does some
queries on it to build some caches in the database, all in one long
transaction. One of those cache updates repeatedly calls a p
I have an application that imports a lot of data and the does some
queries on it to build some caches in the database, all in one long
transaction. One of those cache updates repeatedly calls a plpgsql
function, which internally does some SQL queries. Sometimes this is
much, much slower than
On 31/10/2016 8:26 PM, Melvin Davidson wrote:
I have tried using an event trigger to detect table creation (ie:
tg_event_audit_all ) however, that does not parse the schema_name and
objid
as does pg_event_trigger_dropped_objects(), so I am not sure that is a
practical way to audit.
Event
If I have a query that reads from system tables like pg_class,
pg_namespace, pg_attribute, pg_type, etc. and I'd like to cache the
results in my application is there any fast way to detect when any
changes have been made to these system catalogs? I don't need to know
exactly what has changed.
the transaction block doesn't prevent the constraint violation,
either.
On 28/04/2015 2:16 PM, John McKown wrote:
On Mon, Apr 27, 2015 at 7:45 AM, Evan Martin
postgre...@realityexists.net mailto:postgre...@realityexists.netwrote:
I submitted the following bug report through the web form a few
I submitted the following bug report through the web form a few days
ago. It's causing problems in my application and I've been unable to
find a way to get around it. If someone here, familiar with PostgreSQL
internals, could suggest a workaround I'd really appreciate it!
I have a deferred
Hello,
I'm using PostgreSQL 9.2.8 via Npgsql 2.2.0. When a query times out it
returns error 57014 with the message canceling statement due to
statement timeout. I use the message to detect the timeout and re-try
in some cases. It seems a bit wrong to rely on the message, though - I
presume
Hi All,
I have a database schema where if row is deleted from one table the rows
it references in another table should also be deleted, unless still
referenced by something else.
Eg. Table A has foreign key to table B. When I delete a row from A I
also want to delete the referenced row in
In a nutshell: I think the difficulty of dropping functions is
inconsistent with the difficulty of dropping other objects and I'd like
to see this inconsistency fixed.
So I don't agree with the suggestion of matching function names using a
regex, since that's not supported for other types of
On 04/02/2014 19:56, David Johnston wrote:
No, they cannot. If the arguments change you are dealing with an entirely
new object. And often you end up keeping the old function around for
backward-compatibility.
Of course, I understand that it's a different object, technically, but
from the
Hi All,
Is there any easy way to drop a function (all overloads of it) without
knowing the parameter types? If not, it would be good to see it added.
When I change a function definition I just want to run the SQL script
that defines it and have any existing function replaced. CREATE OR
On 03/02/2014 19:09, Tom Lane wrote:
Evan Martin postgre...@realityexists.net writes:
Is there any easy way to drop a function (all overloads of it) without
knowing the parameter types?
Something along the lines of
do $$
declare fname text;
begin
for fname in select oid::regprocedure from pg_proc
I'd like to rename one of the labels of an enum in PostgreSQL 9.2 This
can easily be done by updating pg_enum, as described at
http://tech.valgog.com/2010/08/alter-enum-in-postgresql.html but I'd
like to understand: what is the danger of doing so? If, as the post
says, the data only references
Johnston wrote:
Evan Martin wrote
Also, even without --clean I get 3 errors:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6755; 2618 4417788 RULE
geometry_columns_delete em
pg_restore: [archiver (db)] could not execute query: ERROR: rule
they can in Postgres, too.
Regards,
Evan
On 04.09.2013 23:26, David Johnston wrote:
Evan Martin wrote
When I use pg_restore with --clean to restore a PostgreSQL 9.2.4database
into a new, blank database it generates thousands of error messages like
this:
pg_restore: [archiver (db)] could
When I use pg_restore with --clean to restore a PostgreSQL 9.2.4database
into a new, blank database it generates thousands of error messages like
this:
pg_restore: [archiver (db)] could not execute query: ERROR: schema
myschema does not exist
Command was: DROP INDEX myschema.some_index;
any existing function with the same signature to be overwritten.
On 04.09.2013 18:20, Adrian Klaver wrote:
On 09/04/2013 07:02 AM, Evan Martin wrote:
Also, even without --clean I get 3 errors:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC
privileges, which I don't think
should be required to drop your own database.
Regards,
Evan
On 20/06/2012 10:51 PM, Sergey Konoplev wrote:
On Tue, Jun 19, 2012 at 1:40 PM, Evan Martin
postgre...@realityexists.net wrote:
When I'm developing against a PostgreSQL database I often drop and re
On 21/06/2012 10:20 PM, Sergey Konoplev wrote:
On Thu, Jun 21, 2012 at 2:03 PM, Evan Martin
postgre...@realityexists.net wrote:
1) The workaround requires extra work for each developer (or at least each
client application) using PostgreSQL, while a fix in PostgreSQL would solve
this once
On 22/06/2012 12:07 AM, Tom Lane wrote:
SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE datname = 'dropme';
ERROR: must be superuser to signal other server processes
As far as that goes, there's a pending patch to reduce the privileges
required to use pg_terminate_backend.
Hi All,
When I'm developing against a PostgreSQL database I often drop and
re-create it and I often find that the drop fails, because it's in use
by other users. This is really annoying, especially when I know full
well there are no other users - it's just me.
I'm aware of the workaround:
Thanks, Tom. You mean this bit, right?
- Seq Scan on _test_pos (cost=0.00..10728.00 rows=1 width=4)
Filter:
((('010120E6101C401C40'::geography
_st_expand(pos, 30::double precision)) AND ...
I tried to find some info on selectivity estimation
I've run into a weird query performance problem. I have a large, complex
query which joins the results of several set-returning functions with
some tables and filters them by calling another function, which involves
PostGIS calls (ST_DWithin). This used to run in about 10 seconds until I
I dug a bit further into this and found that the code was also creating
and releasing a savepoint for each insert (and there were over 10,000 of
them). When I removed that the delay at the end disappeared.
Regards,
Evan
On 11/05/2012 8:26 PM, Simon Riggs wrote:
On 11 May 2012 11:16, Evan
I'm running a bulk import application against PostgreSQL 9.1.3, which
has several stages and each stage follows the same general pattern:
BEGIN TRANSACTION
DELETE (many rows)
CREATE SAVEPOINT
INSERT
...
RELEASE SAVEPOINT
CREATE SAVEPOINT
INSERT
INSERT
... half an hour of inserts later ...
Thanks, I went into that function, added log statements everywhere and
figured which check it's failing on:
!heap_attisnull(func_tuple, Anum_pg_proc_proconfig)
and it's because my real function had this at the end:
SET search_path FROM CURRENT;
which I never imagined would make any
Of course, it seems silly now to not have included SET search_path FROM
current in my post, but I had no idea what was and wasn't critical -
that was the whole problem. Wisdom begins with knowing the right
questions to ask! Yes, I was looking at the same function - even checked
SVN logs to
) OR ('2012-04-01 00:00:00'::timestamp without time zone
(valid_time_end)::timestamp without time zone)))
Any idea what's going on? It seems like the query optimizer randomly
changes its mind about inlining it.
Regards,
Evan
On 2/05/2012 3:34 PM, Evan Martin wrote:
Thanks, Tom (and Chris). Yes
Some of my functions are running much slower than doing the same query
inline and I'd like to know if there's a way to fix that.
I have a number of tables that store data valid at different times. For
each logical entity there may be multiple rows, valid at different times
(sometimes
Thanks, Tom (and Chris). Yes, the EXPLAIN output showed a function scan:
SELECT * FROM thing_asof('2012-04-01') WHERE timeslice_id = 1234
Function Scan on thing_asof (cost=0.25..12.75 rows=5 width=353)
Filter: ((timeslice_id)::integer = 12345)
I replaced the OVERLAPS with and = comparisons
Hi,
Are there any plans to fix the caveats documented in section 5.8.1,
particularly allowing rows in a derived table to satisfy a foreign key
defined on a base table? I know it's on the TODO list (
http://wiki.postgresql.org/wiki/Todo#Inheritance ) - just wondering if
anyone is actually
31 matches
Mail list logo
