Hi all,
I’m migrating a database from 9.2.4 to 9.3.0 and encountering an issue with
an XML field failing to restore.
I’m using pg_dump 9.3.0 as follows (with 9.2.4 on 5433 and 9.3.0 on 5432).
I have verified my paths, it’s definitely using the 9.3.0 binaries. Both
source and target are using UTF8
t.
I suspect this might be resolved in a newer point release of 9.3, however I
don’t have the luxury on this particular host right now.
Any suggestions how I might convince 9.3.0 to accept the dump from 9.2.4?
Cheers,
Tim
Tim Kane
Senior Development Engineer Lead
t: +44 20 7092 8700
m:
blinkbo
>
>
> From: Tom Lane
>
> Hm, can you restore it into 9.2 either?
>
> AFAICS, pg_dump has absolutely no idea that it should be worried about the
> value of xmloption, despite the fact that that setting affects what is
> considered valid XML data. What's worse, even if it were attempting to do
Hi all...
Forgive me if this is not the correct forum for such suggestions.
I'm about to begin using the custom format options for pg_restore, so that
we can leverage the performance gains from --jobs options.
However I would like to be able to specify the tablespace for individual
tables/indexes
Hi all,
I've discovered one of our databases has managed to get into a state where
it is violating a foreign key constraint.
The facts are these:
Table B (row_id) references Table A (row_id).
Table B contains multiple row_id's that do not exist in Table A.
There also exists a BEFORE INSERT OR UP
Hi all,
I seem to be having problems constructing a query that combines the use of
distinct, order by, and collate key words.
For instance:
# select distinct(value) from properties order by 1 collate "C";
ERROR: collations are not supported by type integer
LINE 1: ... distinct(value) from pro
aver wrote:
> On 06/20/2013 07:05 AM, Tim Kane wrote:
>
>> Hi all,
>>
>> I seem to be having problems constructing a query that combines the use
>> of distinct, order by, and collate key words.
>>
>> For instance:
>>
>> # select distinct(value
Hi all,
I'm currently playing a few ideas out in my head and wanted to see if this
was feasible. There may be some limitation I am not aware of.
I would like to create a foreign table into a hot standby instance using
FDW's. I appreciate that the hot standby is always going to be read-only,
and
Brilliant. Thanks Michael. That looks great.
On Thu, Jul 18, 2013 at 11:54 PM, Michael Paquier wrote:
>
>
> On Thu, Jul 18, 2013 at 11:15 PM, Tim Kane wrote:
> > Hi all,
> >
> > I'm currently playing a few ideas out in my head and wanted to see if
>
Okay, so I've played with this a bit more. I think I have it cracked.
I had to increase random_page_cost and significantly reduce
effective_cache_size in order for the planner to prefer a sequential scan. (It
turns out this is what the 8.3.8 instance was doing all along, so it's not
anything s
Ahh, thanks Tom.
I hadn't seen your email before I posted my own followup.
I guess the clustering approach managed to work around the need to mess with
the statistics target. I did previously increase the target to 1000 (from 100)
for that field and had no impact, but this is an aspect of tuni
ing
everything together. Cheers.
Tim
On 11 Aug 2013, at 01:38, Tom Lane wrote:
> Tim Kane writes:
>> I guess the clustering approach managed to work around the need to mess with
>> the statistics target. I did previously increase the target to 1000 (from
>> 100) for that fie
Hi all,
It seems like it isn't possible to perform a wildcard LIKE evaluation
against array objects. Is this a bug, or just unsupported?
See the queries in bold, that I would have expected to return a value.
Postgres 9.1.9
=# create temp table ids (id varchar(12)[]);
CREATE TABLE
Time: 185.516
EACH style traversal within plpgsql
(which is fine, as this is all to be used within a function anyway).
On Wed, Aug 14, 2013 at 10:55 AM, Pavel Stehule wrote:
> Hello
>
> http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns
>
> Regards
>
> Pavel Stehule
>
&
Thanks Jeff. These queries in particular relate to a set of data that is
rebuilt on a periodic basis. For all intents and purposes, the data is
newly populated and unlikely to reside in cache - hence the need to
perform my tests under similar conditions.
It's probably better than I adjust the ran
Hi all,
I'm seeing sporadic (but frequent) UDP buffer drops on a host that so far
I've not been able to resolve.
The drops are originating from postgres processes, and from what I know -
the only UDP traffic generated by postgres should be consumed by the
statistics collector - but for whatever r
e, Apr 18, 2017 at 8:05 AM Tim Kane wrote:
> Hi all,
>
> I'm seeing sporadic (but frequent) UDP buffer drops on a host that so far
> I've not been able to resolve.
>
> The drops are originating from postgres processes, and from what I know -
> the only UDP traf
13 minutes) but
I can't correlate them with any kind of activity (and if I'm honest, it's
possibly starting to drive me a little bit mad).
On Tue, Apr 18, 2017 at 2:53 PM Tim Kane wrote:
> Okay, so I've run an strace on the collector process during a buffer drop
> event
drops.. At this point, I'm not sure I
> expected it to. They tend to occur semi-regularly (every 8-13 minutes) but
> I can't correlate them with any kind of activity (and if I'm honest, it's
> possibly starting to drive me a little bit mad).
>
>
>
>
> O
Hi all,
It seems the behaviour of the command / -c option is such that it will
ignore any variables set on the command-line.
:~ psql --variable TESTVAR='123' --command 'select :TESTVAR;'
ERROR: syntax error at or near ":"
LINE 1: select :TESTVAR;
^
:~$ psql --variable TESTVAR='
Ahh, ok. It is documented. I'll get back in my box :)
On 02/09/2013 16:04, "Adrian Klaver" wrote:
>On 09/02/2013 07:54 AM, Tim Kane wrote:
>> Hi all,
>>
>> It seems the behaviour of the command / -c option is such that it will
>> ig
Hi all,
I have a fairly simple query, running on a particularly large table. For
illustration:
echo "select * from really_big_table;" | psql my_database > /dev/null
When I monitor the memory usage of the psql session, it continually grows.
In fact, for this particularly large table it grows
Ahh. All these years (albeit sporadic), I never knew about FETCH_COUNT.
That makes sense. Thanks muchly.
On 06/09/2013 14:11, "Suzuki Hironobu" wrote:
>(13/09/06 21:06), Tim Kane wrote:
>> Hi all,
>>
>> I have a fairly simple query, running on a
On 18/09/2013 14:44, "Rowan Collins" wrote:
>
>Running an EXPLAIN on the exact query should give you the plan. An
>EXPLAIN ANALYZE would have to wait for the query to complete either way,
>so you wouldn't be able to get it mid-way through a running process.
>
>If you don't know the exact query
I have a question regarding the behaviour of CREATE DATABASE when used with
TEMPLATE and TABLESPACE options together.
The documentation describes the tablespace parameter as:
The name of the tablespace that will be associated with the new database, or
DEFAULT to use the template database's table
According to the docs, the output of a copy command should return a COPY
count. It turns out this only happens when copying from a file, and not
from STDIN.
> Outputs
> On successful completion, a COPY command returns a command tag of the form
> COPY count
> The count is the number of rows copie
Hi all,
I¹ve been looking for a facility to specify the tablespace of an index that
is implicitly created when adding a PK constraint. It turns out that this
does indeed exist, though I could find no mention of it in the
documentation.
> ALTER TABLE {table} ADD CONSTRAINT {constraint} PRIMARY KEY
>
> This is indicated in the "CREATE TABLE" documentation (to which much of the
> "ALTER TABLE" documentation defers).
So it is My bad, apologies for the noise.
Hi all,
I¹m having difficulty using variable interpolation within psql, where that
variable is within a table name
Like so..
=# set MM 201310
=# select :MM;
?column?
--
201309
(1 row)
=# alter table my_table rename to my_table_:MM_raw;
ERROR: syntax error at or near
On 08/11/2013 16:47, "Adrian Klaver" wrote:
>>
>test=> \set tbl_name 'my_table_':MM'_raw';
>
>test=> alter table my_table rename to :tbl_name;
>ALTER TABLE
>
>test=> \d my_table_201310_raw
>Table "public.my_table_201310_raw"
> Column | Type | Modifiers
>+-+---
>
HI all,
Is anyone aware of cases where psql will occasionally return an exit status
of 1, despite there being no obvious error condition?
I have a regular (daily) cron that executes a fairly simple script of the
form:
> psql --set ON_ERROR_STOP=1 -h $DB_HOST -U $DB_USER -a -v DATE="'${DATE}'" -
I should have mentioned, psql client and server version 9.2.4
From: Tim Kane
Date: Wednesday, 4 December 2013 10:59
To: pgsql-general General
Subject: unexplainable psql exit status 1
HI all,
Is anyone aware of cases where psql will occasionally return an exit status
of 1, despite there
> According to the documentation you quoted, it would be useless to set
> log_statement=all, because the problem is on the client end.
>
> Shell debugging sounds slightly more promising, but it will probably
> not tell you much more than that psql returned with exit code 1.
>
> A quick look at t
Hi all,
The past few days I’ve been encountering the following error, followed by a
full db restart and recovery
2013-12-16 07:12:53 GMT LOG: could not write temporary statistics file
"pg_stat_tmp/pgstat.tmp": No space left on device
This occurs at a time of moderate load, during the same set
Are these truly orphaned files that postgresql no longer needs (possibly due
to a crash) ?Is there a mechanism to scan the PG_DATA directories to
locate such files?
FYI, we’re running 9.2.4
Thanks again,
Tim
From: Jeff Janes
Date: Monday, 16 December 2013 17:32
To: Tim Kane
Cc: pgs
HI all,
I have a fairly complex python script which calls a number of ‘psql’
sessions via a subprocess to perform bulk load operations.
The bulk loads are generated on the fly, and are written directly to the
stdin of the subprocess.
I would like to rely on the ON_ERROR_STOP=1 functionality to f
Thanks Chris. I didn’t realise psycopg2 had a COPY interface, that’s quite
handy. I’ll have a play, cheers.
Tim
From: Chris Curvey
Reply-To:
Date: Friday, 27 December 2013 21:18
To: Tim Kane
Cc: pgsql-general General
Subject: Re: [GENERAL] ON_ERROR_EXIT and interactive mode (or, how
Hi all,
Take the following scenario
I have a set of partitions inherited from a parent table, called streams.
One of the properties of these tables is a timestamp field, nothing fancy
about it.
I also have a qualified index on this field.
I’ve noticed that if I perform the following query, the
values.
Granted, an expression based index would solve this.
It just seemed an opportunity to open up opportunities for the QEP – at
least for the simple case.
Cheers,
TIm
From: Sameer Kumar
Date: Thursday, 20 February 2014 07:40
To: Alban Hertroys
Cc: Tim Kane , pgsql-general General
Hi all,
I decided to have a play with postgresql-9.4devel as I wanted to explore the
functionality provided by
unnest (anyarray, anyarray [, …])
I’ve taken the nightly snpahost, compiled, installed.. All good. (Obtained
from http://ftp.postgresql.org/pub/snapshot/dev/ )
However it seems the ex
ction signature to change. Thanks for
setting me straight.
Tim
From: Tom Lane
Date: Monday, 10 March 2014 15:10
To: Tim Kane
Cc: pgsql-general General
Subject: Re: [GENERAL] Playing with 9.4devel - unnest
Tim Kane writes:
> I decided to have a play with postgresql-9.4devel as I wanted to
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
Any suggestions? Or should the parser be allowing a subquery as a parameter
to unnest?
Tim
From: Tim Kane
Date: Monday, 10 March 2014 15:26
To: Tom Lane
Cc: pgsql-general
HI all,
I’ve had an interesting problem trying to perform an UPDATE based on the
results of processing an xpath against a field of type xml.
Specifically, my query would fail with:
> ERROR: could not parse XML document
> DETAIL: line 1: Start tag expected, '<' not found
I thought this strange
Hi all,
I’ve been using hstore to record various key/val pairs, but I’ve noticed it
consumes a lot more disk than I would have expected.
I don’t have any hard figures to illustrate, but empirical testing has shown
that if I record these pairs as traditional column based fields, I can save
a signif
Hi all,
I’ve just noticed that some DDL statements will block if there are other
queries accessing the relation in question, even where those queries are not
dependant on the underlying structures (such as a new index, created after
the initial query).
For instance…
> SELECT long_running_query F
can be determined that no currently running query will be affected?
Tim Kane
Senior Development Engineer Lead
t: +44 20 7092 8700
m:
blinkbox music - the easiest way to listen to the music you love, for free
www.blinkboxmusic.com
Thanks Tom, I see there are a lot of considerations. I guess I just found it
to be an interesting scenario, although not entirely unexpected. It’s not a
big deal of course.
Cheers,
Tim
From: Tom Lane
Date: Tuesday, 6 May 2014 15:05
To: Tim Kane
Cc: pgsql-general General
Subject: Re
The subject line may not actually describe what I want to illustrate…
Basically, let’s say we have a nicely partitioned data-set. Performance is a
net win and I’m happy with it.
The partitioning scheme is equality based, rather than range based.
That is, each partition contains a subset of the da
>
> From: Tom Lane
> David G Johnston writes:
>> Two approaches:
>> 1. Standard virtual column name that, when used, gets rewritten into a
>> constant that is stored at the table level.
>> 2. A way for a column's value to be defined as a function call.
>
> Recent versions of the SQL spec
HI all,
I’m curious about materialised views.
I was just setting something up, and noticed there is no support for
UNLOGGED materialised views
(http://www.postgresql.org/message-id/E1UZNrD-0008Eh-83@gemulon.postgresql.o
rg)
I also notice that the creation of an unlogged table appears to be a lot
>
>> Aside from the convenience of the REFRESH functionality, are there any other
>> factors I should consider?
> An exclusive lock is taken on the materialized view during a REFRESH
> operation, blocking an read or write queries attempted on them. You
> can tackle this limitation in the upcomi
51 matches
Mail list logo
