Hi
On Tue, Oct 21, 2014 at 12:53 PM, Ilya I. Ashchepkov wrote:
> I wrote a function and during testing it I came across the strange
> behaviour.
> Function runtime is about 200ms first 5 times, 6th and futher calls takes
> ~22000 ms.
> I simplified my schema, you can see it in attached file.
> I'
On Mon, Sep 15, 2014 at 8:22 AM, cowwoc wrote:
> I took a look at the PL/Java project and it looked both incomplete and dead,
> yet other languages like Javascript are taking off. I would have expected to
> see very strong support for Java because it's the most frequently used
> language on the se
On Wed, Aug 13, 2014 at 9:44 AM, Albe Laurenz wrote:
> Set wal_buffers to 16MB so that a whole WAL segment will fit.
No need, wal_buffers is automatically tuned now. If your
shared_buffers is 512MB or larger, wal_buffers will be 16MB.
Regards,
Marti
--
Sent via pgsql-general mailing list (pgs
On Thu, Jul 31, 2014 at 9:38 PM, Kynn Jones wrote:
> Does PostgreSQL have a good way to enforce the uniqueness of super_id values
> across multiple tables?
Well that's easy: no.
Regards,
Marti
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscr
On Thu, Jun 26, 2014 at 5:49 PM, Shaun Thomas wrote:
> Then you create a job that runs however often you want, and all that job
> does, is move old rows from my_table, to my_table_stable. Like so:
>
> BEGIN;
> INSERT INTO my_table_stable
> SELECT * FROM ONLY my_table
> WHERE date_col >= now() - I
On Fri, Jun 20, 2014 at 1:47 AM, Borislav Ivanov wrote:
> If your database is relatively small, I would recommend
> http://www.pgbarman.org/. It does binary backup and will take care of your
> WAL files. The laster version of pgbarman can also take backups from a slave
> using pgespresso extension
On Tue, Mar 11, 2014 at 9:17 PM, Kevin Grittner wrote:
> I haven't been able to think of a way to create circular references
> among a set of materialized views, short of committing violence
> against the system catalog tables directly. What have I missed?
Not directly, but you can create circles
On Fri, Feb 14, 2014 at 7:26 PM, Reece Hart wrote:
> I should be able to chase pg_depends entries to create this ordering, right?
Not always, there may be circular dependencies between them.
Regards,
Marti
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes t
On Wed, Feb 12, 2014 at 10:46 PM, Alvaro Herrera
wrote:
> Would it be more useful to report the test as failed and continue with
> other tests?
Yeah, I think so, I'm planning to code this in the week. It's harder
than it sounds because the alarm() timer is still ticking. On POSIX it
can be cancel
On Tue, Feb 11, 2014 at 12:20 AM, Marti Raudsepp wrote:
> This is on Ubuntu 13.10 (kernel 3.11) with XFS (mount ed with noatime,
> no other customizations).
I managed to track this down; XFS doesn't allow using O_DIRECT for
writes smaller than the filesystem's sector size (probab
Hi list,
I'm in the middle of setting up a new machine and there's something
odd in pg_test_fsync output. Does anyone have ideas why open_sync
tests would fail in the middle?:
4 * 4kB open_sync writes 89.322 ops/sec 11195 usecs/op
8 * 2kB open_sync writes write
On Thu, Jan 23, 2014 at 3:46 PM, Bill Moran wrote:
> It was suggested that
> leaving the constraints as NOT VALID might affect the planner, causing
> it to use less optimal plans because it doesn't think it can trust
> the constraint. Is this true?
AFAICT the planner doesn't currently rely on FO
On Wed, Jan 22, 2014 at 6:59 PM, Florian Weimer wrote:
> This happens with postgresql-server-9.3.2-2.fc20.x86_64.
>
> I track this down to a lower-than-usual setting of work_mem, to 1MB, after
> the upgrade to 9.3.
>
> With work_mem set to 8MB, the query completes in a reasonable time frame,
> wit
On Fri, Jan 17, 2014 at 10:20 AM, Laurence Rowe wrote:
> I'm trying to unpack a json array into it's constituent text values so I can
> join them to a table. I can successfully unpack json values, but am having
> trouble converting these to text so I can cast them to the UUIDs needed for
> the joi
On Fri, Jan 17, 2014 at 10:20 AM, Laurence Rowe wrote:
> I'm trying to unpack a json array into it's constituent text values so I can
> join them to a table. I can successfully unpack json values, but am having
> trouble converting these to text so I can cast them to the UUIDs needed for
> the joi
On Wed, Jan 15, 2014 at 7:36 AM, ambilalmca wrote:
> can you tell me the full query for that? because in pg_stat_all_tables
> contains many fields. i dont know whats the correct one to get the result.
*Number of cached blocks read,
Number of cached index blocks read,
They're in pg_st
On Tue, Jan 14, 2014 at 2:36 PM, Keith Minsel wrote:
> Can anyone describe how Postgres generates session identifiers and how
> Postgres uses a session identifier for community action protection?
PostgreSQL is a database system, it does not "generate session identifiers".
You probably have an ap
On Fri, May 31, 2013 at 7:32 PM, wrote:
> 1.) Is there any way to clear the cache so that we can ensure that when we
> run "explain analyze" on a query and make some minor adjustments to that
> query and re-execute, the plan is not cached.
PostgreSQL doesn't cache query plans if you do a normal
On Thu, Dec 20, 2012 at 3:28 AM, Robert James wrote:
> Is there an aggregate that will return an arbitrary instance? That is,
> not necessarily the max or min, just any one? (Which might perform
> better than max or min)
>
> More importantly:
> Is there one which will return an arbitrary instance
On Wed, Oct 31, 2012 at 8:08 PM, Raghavendra
wrote:
> False represented by zero bytes and True by 1 byte with value 1.
This is not true AFAIK. Both boolean TRUE and FALSE values require 1 byte.
A NULL value is zero bytes (though it still consumes 1 bit in the null
bitmap). This is true for all t
On Fri, Sep 7, 2012 at 12:22 AM, Aram Fingal wrote:
> Should I write a script which drops all the indexes, copies the data and then
> recreates the indexes or is there a better way to do this?
There's a pg_bulkload extension which does much faster incremental
index updates for large bulk data im
On Tue, May 15, 2012 at 12:52 PM, Catalin(ux) M. BOIE
wrote:
> The old_stats is so big that I cannot afford to add a check constraint.
> But, I know that all values of the itime field are before 2012_04, so, would
> be great if I could run something like:
If you Really Really need it and if you'r
On Sat, May 26, 2012 at 7:04 AM, Andreas wrote:
> I'd like to find ressources to look up how one can do X in MSSQL when one
> knows how it is done in PG's SQL and the other way around.
Here's another resource, going into more detail:
http://troels.arvin.dk/db/rdbms/
Regards,
Marti
--
Sent via
On Fri, May 25, 2012 at 12:56 PM, Craig Ringer wrote:
> I just had the ... pleasure ... of using Windows with Pg again and was in a
> usability review frame of mind. I landed up trying to restore my database
> using PgAdmin-III, and was astonished at how difficult and painful it was.
> The issues
On Thu, May 17, 2012 at 8:40 PM, Seref Arikan wrote:
> Is there a glaring error in my approach? Should I be better off with another
> SQL query, or Ltree/XPATH queries?
For the particular query you posted, I would suggest the following indexes:
(rm_type_name, payload_id, parent_feature_mapping_i
On Thu, Mar 22, 2012 at 00:20, Martijn van Oosterhout wrote:
> That, and a good RAID controller with BBU cache will go a long way to
> relieving the pain of fsync.
Well a BBU cache RAID is helpful, but fsyncs are a minor problem in
data warehouse workloads, since inserts are done in large bulks a
On Wed, Mar 21, 2012 at 11:10, Vincent Veyron wrote:
> However, I once read that the real reason is that mysql was available
> when ISPs came of existence, circa 1995. It lacked important features of
> an RDBMS (you can google the details), but it was enough to satisfy the
> needs of php scripts f
On Fri, Mar 9, 2012 at 19:16, Randy Ficker wrote:
> Most writing transactions are using the REPEATABLE READ isolation
> level (the SERIALIZABLE level is not used at all).
Are you 100% sure about this? A major thing that changed in 9.1 was
implementation for proper SERIALIZABLE isolation, which co
On Wed, Mar 7, 2012 at 16:23, Martin Gregorie wrote:
> Is it possible to reinstate 'template1' and all its works without
> reinitialising the database from scratch. It was suggested to me that
> running "initdb" might fix it while leaving my data in place though my
> source was uncertain about its
On Tue, Feb 28, 2012 at 17:41, Daniele Varrazzo
wrote:
> On Tue, Feb 28, 2012 at 2:15 PM, Marti Raudsepp wrote:
>> On the Python end, you have to call psycopg2 connection.poll() method
>> periodically or in response to select() activation
>> There's an example here:
&
On Tue, Feb 28, 2012 at 04:34, Adam Bruss wrote:
> The problem is the
> Handle count on the Windows System process of the server, image name
> c:\windows\system32\ntoskrnl.exe, accumulates over time and the handles
> never get released. This causes the handle count to go to about 130,000 at
> whic
On Tue, Feb 28, 2012 at 13:07, Andre Lopes wrote:
> This is a mixed question Python/PostgreSQL... I need to get a signal
> in my python application when a new insert is done. How can this be
> done, any clues?
As depesz mentioned, you can use the LISTEN and NOTIFY commands for
this asynchronous s
On Mon, Feb 27, 2012 at 00:54, Tim Uckun wrote:
> The main reason I am not using COPY right now is because postgres will
> not allow unprivileged users to issue the COPY from FILENAME. The
The reason for that is a good one -- that would allow these users to
read any file from the disk, under Pos
On Thu, Feb 23, 2012 at 08:02, Dmytrii Nagirniak wrote:
> Thanks. So far I tried:
>
> fsync = off
> full_page_writes = off
>
> It seems it got a *little* faster (down to ~65 seconds from ~76) but is till
> too far from my target of ~34 secs.
If you have lots of very simple queries, then usually m
On Fri, Feb 17, 2012 at 18:19, Scott Marlowe wrote:
> Have you tried casting to varchar(1000) or something like that?
MySQL's CAST() doesn't accept varchar or varchar(N). It accepts char,
which behaves like varchar.
Regards,
Marti
--
Sent via pgsql-general mailing list (pgsql-general@postgresq
On Fri, Feb 17, 2012 at 17:31, David W Noon wrote:
> The words to search for are "Cartesian product". This is the way
> RDBMSes have handled unconstrained implicit joins since the days of
> SQL/DS.
Yeah, that's what Jack was confused about -- it's actually *not* a
cartesian product. It simply st
On Fri, Feb 17, 2012 at 17:13, Adrian Klaver wrote:
> Why not use:
> " SELECT * FROM WHERE CAST(Table.ID as TEXT) LIKE '1%' "
>
> as the MySQL query also? MySQL supports the CAST function.
Nope, trying to use CAST() in an interoperable manner is a lost cause.
Sadly MySQL and PostgreSQL don't ag
On Thu, Feb 16, 2012 at 18:53, sodik wrote:
> is there any way how to check that postgres 9.1 is currently in backup
> mode?
You can check the existence of the "backup_label" file in your data
directory. If it's there, then a backup is in progress.
If your monitoring system is on a diferent serv
2012/2/15 Vojtěch Rylko :
> this query performed so long and blocked table so I had to interrupt it. Is
> there any way how to drop large indexes in non-blocking or /faster/ way?
Usually the problem is not with the size of the index -- but some
other running transactions that hold a read lock on t
On Wed, Feb 15, 2012 at 19:25, Marti Raudsepp wrote:
> VACUUM FULL is extremely inefficient in PostgreSQL 8.4 and older.
Oh, a word of warning, PostgreSQL 9.0+ has a faster VACUUM FULL
implementation, but it now requires twice the disk space of your table
size, during the vacuum proc
On Wed, Feb 15, 2012 at 18:46, Asher Hoskins wrote:
> My problem is that the autovacuum system isn't keeping up with INSERTs and I
> keep running out of transaction IDs.
This is usually not a problem with vacuum, but a problem with
consuming too many transaction IDs. I suspect you're loading that
On Sat, Feb 11, 2012 at 12:42, Jasen Betts wrote:
> There is no need. now() is tagged as stable. it will only be executed once.
> the planner will figure this out for you.
Actually that's not always true. In index condition arguments, the
expression would indeed be executed just once. But in filt
On Mon, Feb 13, 2012 at 15:48, JG wrote:
> I would like to ask weather PostgreSQL does database denormalization at
> runtime.
>
> To specify further, the question is, can I count on PostgreSQL to denormalize
> the database when it would be better for the performance, or should I always
> denorm
On Wed, Feb 8, 2012 at 15:37, 84.le0n <84.l...@gmail.com> wrote:
> This is an interesting solution, but I know a little bit PL/pgSQL and I
> don't know how provide SOUNDEX version in PL/pgSQL, I don't know SOUNDEX
> algo too.
> How can I provide soundex in PL/pgSQL ?
I wrote and posted a PL/pgSQL
On Mon, Feb 6, 2012 at 23:48, 84.le0n <84.l...@gmail.com> wrote:
> Now the problem is, how can I enable SOUNDEX inside database ? Is
> there a way to use it without running contrib files ?
This is a bit ugly, but you could write the function yourself in the
PL/pgSQL procedural language, which is e
On Mon, Jan 30, 2012 at 20:55, Tulio wrote:
> I have 2 servers, working with Hot-Standby and Streaming Replication...
> and when we executed some query much large returns a message..
> "canceling statement due to statement timeout"
> I want know, how can I calculate the better value to
> "vacuum_
On Tue, Jan 31, 2012 at 00:41, Jerry Richards
wrote:
> I just installed postgreSQL 9.1 and noticed it hard-codes the folder
> /var/lib/pgsql/9.1 and it hard-codes the service name to be postgresql91.
> Why is the hard-coded version included in the naming?
Note that this is done by Linux distribu
On Wed, Dec 21, 2011 at 15:24, salah jubeh wrote:
> Why I can not use it directly as shown in the example below.
> why a and b can not be used as aliases for the column names ? I want to know
> the theoretical reason behind it?
As far as I know, that's how the SQL standard specifies it. PostgreS
On Mon, Dec 19, 2011 at 18:14, Pavel Stehule wrote:
> you can disable check per session if you need
> ALTER TABLE ... DISABLE TRIGGER ALL;
This is NOT a per-session command, this applies to all connections and
queries, and may allow incorrect data into the table. Don't do this!
(And it doesn't ad
On Mon, Dec 19, 2011 at 06:32, Merlin Moncure wrote:
> that would require
> that the planner have very special understanding of the internal
> workings of aggregate functions. There are a couple of cases where
> the planner *does* have that function, for example it can convert
> max(v) to 'order
On Thu, Dec 15, 2011 at 18:10, Robert James wrote:
> How do I do the equivalent of an ANY() or ALL() in PG Aggregate SQL?
Note that in many cases, writing an EXISTS(SELECT ...) or NOT
EXISTS(...) subquery is faster, since the planner can often optimize
those to a single index access -- whereas an
On Wed, Dec 14, 2011 at 08:38, AI Rumman wrote:
> Is it possible to find out, when was pg_stat_reset() used in my server for
> the last time?
> I am using Postgresql 9.2 and I need to find out unused index.
I assume you mean PostgreSQL 9.1.2
The pg_stat_database system view has a 'stats_reset' c
On Fri, Dec 2, 2011 at 16:16, Torsten Zuehlsdorff
wrote:
> But i clearly have a missunderstanding of other chars, like umlauts or utf-8
> chars. This, for example, should return a 'ö':
>
> # SELECT chr(x'C3B6'::int);
> chr
> -
> 쎶
> (1 row)
That gives you the Unicode codepoint C3B6, but %C3
On Mon, Oct 24, 2011 at 23:23, Szymon Guz wrote:
> String query1 = "SELECT * FROM information_schema.schemata WHERE schema_name
> = ?";
> When I query the database using psql, both queries return sensible data
> (even when I prepare statements in postgres).
> I'd like to use information_schema r
On Mon, Oct 24, 2011 at 10:27, Javier Amor garcia wrote:
> sometimes I get encoding errors when inserting a s a encoded URL in a text
> field.
You forgot the most important thing: *What's* the error that you get?
> http://www.formacion.aimplas.es/_Documentos/2011/FORMACIÓN%20ABIERTA/Folleto%20Es
On Thu, Sep 29, 2011 at 15:48, Jon Nelson wrote:
> especially if my attempt
> to do so kills the backend I am using (which triggers a shutdown of
> all other backends, no?).
No, this is just an ereport(ERROR) that's handled gracefully by
rolling back the transaction.
Regards,
Marti
--
Sent via
On Thu, Sep 29, 2011 at 15:15, Gregor Vollmer
wrote:
> We do not do any arithmetic on the columns, only saving and retrieval, is
> decimal as fast as bigint in that case?
It's slightly slower because numeric is passed around by pointer, not
by value. Bigint is by-value in 64-bit Postgres versions
On Tue, Sep 27, 2011 at 20:01, David North wrote:
> testdb=# select * from problem_table;
> ERROR: invalid memory alloc request size 2003676411
> Is there some reason why my data can be stored in <1GB but triggers the
> allocation of 2GB of memory when I try to read it back? Is there any setting
Alban, you forgot to reply to the mailing list. Please use the "reply
to all" button in your email client. :)
On Tue, Sep 27, 2011 at 14:21, Alban Hertroys wrote:
> If performance is an issue, I'd suggest coding it as a C function.
>
> A quick google search turned up:
> http://discuss.fogcreek.co
On Tue, Sep 27, 2011 at 13:00, Harald Fuchs wrote:
> Pavel Stehule has found a better solution for that:
>
> CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$
> SELECT string_agg(substring($1 FROM i FOR 1), '')
> FROM generate_series(length($1), 1, -1) g(i)
> $$ language sql;
I don't
2011/9/25 pasman pasmański :
> My english is not perfect, by accumulative i think about monotonically
> increasing function.
>
> It works that for clause WHERE f(x)=const:
> 1. Read root page of index_on_x and get x1 ... Xn
> 2. Calculate f(x1) ... f(xn) for this page
> 3. When f(x1)<=const<= f(xn)
On Tue, Sep 27, 2011 at 01:43, Edson Carlos Ericksson Richter
wrote:
>> create index on foobar (txt text_pattern_ops); create index on foobar
>> (reverse(txt) text_pattern_ops);
>
> I got the following error:
>
> ERROR: function reverse(text) does not exist
> Hint: No function matches the given na
On Mon, Sep 26, 2011 at 15:16, Edson Carlos Ericksson Richter
wrote:
> select * from notafiscal where numeroctc like ‘POA%34345’;
>
> Prefix is normally 3 characters, suffix varyies.
>
> Is Postgresql 9.0.4 able to use an BTREE index on notafiscal.numeroctc to
> execute this query?
As mentioned
On Mon, Sep 26, 2011 at 18:59, Andy Chambers wrote:
> ...and I want to select the data from that table, adding a "group_id"
> column, and a "record_id" column. The "group_id" would start at 1,
> and increment by 1 every 100 rows, and the "record_id" would increment
> by 1 every row, but restart a
On Sun, Sep 25, 2011 at 00:07, Gregg Jaskiewicz wrote:
> My apps share same databases, so no good in that.
How about different users? You can create a separate user for each
application, and then GRANT them access to a single role.
Regards,
Marti
--
Sent via pgsql-general mailing list (pgsql-g
On Sat, Sep 24, 2011 at 17:16, Roger Niederland wrote:
> Just did not expect that the compression would be removed for plain files.
Agreed, I'd say this is a regression that I would like to see
addressed in PostgreSQL 9.1.1. I'm sure you won't be the only to be
surprised by this.
The fact that t
On Fri, Sep 23, 2011 at 13:34, Gregg Jaskiewicz wrote:
> Basically, I got bunch of local processes connecting to postgresql,
> need to aggregate some sort of report about number of connections and
> its origin every so often.
The pg_stat_activity system view gives you the database name (datname)
On Tue, Sep 20, 2011 at 13:46, Andrew Rose wrote:
> Or to put the question another way, is the ordering of transactions on the
> active and standby servers guaranteed to be the same?
Yes. The WAL serializes the order of transactions. It is applied to
slaves in the same order that it's written on
On Tue, Sep 20, 2011 at 16:12, Marti Raudsepp wrote:
> On Tue, Sep 20, 2011 at 15:35, Vincent de Phily
> wrote:
>> The explain output will tell me it's using the index on t1's id, but it tells
>> me nothing about the seqscan that happens on t2 (because I forgot
On Tue, Sep 20, 2011 at 15:35, Vincent de Phily
wrote:
> The explain output will tell me it's using the index on t1's id, but it tells
> me nothing about the seqscan that happens on t2 (because I forgot to add an
> index on t2.ref).
+1 for a TODO on adding foreign key trigger time to EXPLAIN ANAL
2011/9/17 pasman pasmański :
> Trigger may store timestamp to other table "timestamps". You join then
> xmin with xmin from this table to access timestamp.
But note that this is not reliable. Vacuum *does* change xmin during
the freeze process, as does a pg_dump&reload of data. Possibly other
case
On Sat, Sep 17, 2011 at 16:57, Anthony DeBarros wrote:
> Anyone?
Didn't you get the reply by Toby Corkindale?
If your database is small (a few gigabytes or less), then
pg_upgradecluster is the easiest way to upgrade on Ubuntu.
Regards,
Marti
--
Sent via pgsql-general mailing list (pgsql-gener
On Wed, Sep 14, 2011 at 21:05, Fabrízio de Royes Mello
wrote:
> postgres@bdteste=# SELECT array_upper(ARRAY['foo', 'bar'], 1);
On Wed, Sep 14, 2011 at 21:09, Merlin Moncure wrote:
> select count(*) from unnest(_array_);
On Wed, Sep 14, 2011 at 21:15, Steve Crawford
wrote:
> Look at array_dims,
On Fri, Sep 16, 2011 at 21:39, Raghavendra
wrote:
> We can get a Transaction ID, but not the transaction timestamp when it
> performed.
Short answer: You can't. Instead, add a new "timestamptz default
now()" column, that will get you the time of the insert.
If you want the update time, create a
Hi,
On Tue, Jul 5, 2011 at 09:50, Yan Cheng CHEOK wrote:
> The essential difference between inet and cidr data types is that inet
> accepts values with nonzero bits to the right of the netmask, whereas cidr
> does not.
Say, if you have a /8 netmask, the 'cidr' type requires that all the
24 rig
2011/6/30 Miroslav Šulc :
> is it correct behavior that when i issue command "VACUUM VERBOSE", i get
> output with DETAIL info, but when i issue "VACUUM FULL VERBOSE", no
> details are displayed?
Yes, this seems OK. PostgreSQL 9.0 has a new implementation of VACUUM
FULL, which behaves more like CL
On Thu, May 12, 2011 at 17:23, Phoenix Kiula wrote:
> Been reading some old threads (pre 9.x version) and it seems that the
> consensus is to avoid doing massive deletes from a table as it'll
> create so much unrecoverable space/gaps that vacuum full would be
> needed. Etc.
Just running DELETE wi
77 matches
Mail list logo
