On Feb 25, 2011, at 11:45 AM, zab08 wrote:
> run :
> SELECT b.id, array_accum(s.id), array_accum(s.name)from big b, sm s where
> b.id = s.big_id group by b.id;
> (ps: array_accum is aggregate in
> http://www.postgresql.org/docs/9.0/static/xaggr.html)
>
> id | array_accum | array_accum
> --
On 02/24/11 10:55 PM, Yang Zhang wrote:
For various workloads, compression could be a win on both disk space
and speed (see, e.g.,
http://blog.oskarsson.nu/2009/03/hadoop-feat-lzo-save-disk-space-and.html).
I realize Postgresql doesn't have general table compression a la
InnoDB's row_format=com
For various workloads, compression could be a win on both disk space
and speed (see, e.g.,
http://blog.oskarsson.nu/2009/03/hadoop-feat-lzo-save-disk-space-and.html).
I realize Postgresql doesn't have general table compression a la
InnoDB's row_format=compressed (there's TOAST for large values and
use these sqls:
CREATE TABLE big(id serial, name text);
CREATE TABLE sm(id serial, big_id integer, name text);
INSERT into big (id, name) VALUES (1, 'big1');
INSERT into big (id, name) VALUES (2, 'big2');
INSERT into sm(id, big_id, name)VALUES (2, 1, 'sm1');
INSERT into sm(id, big_id, name)VALU
I like being emailed when my job runs. It tells me how big the backup is and
whether it passed or failed. I use cruisecontrol and ant on a regular basis so
it was a natural choice. The most time consuming part was learning how to use
pg_dump.
-Original Message-
From: pgsql-general-ow...
"David Johnston" writes:
> Now, if I simply replace the original FROM clause with the view definition
> (i.e., SELECT * FROM (SELECT ... ) alias WHERE ) I get:
> [ a different plan ]
> I now have index scans on both "filetaskinstance" and "filereference" - but
> all I appeared to do is the same as
On 02/24/11 7:09 PM, Adam Bruss wrote:
I'm using cruisecontrol and ant to back up our database at certain
times on certain days of the week. Cruisecontrol sends out completion
emails when it's done. I don't think pgsql has a good built in way to
schedule backups. Cruisecontrol offers supreme fl
Hi all
I just found out that PHP 5.3's PostgreSQL PDO driver (as used by
Drupal) is broken by the Postgresql 8.0 transition from octal to hex
encoding for bytea. It passes the raw hex through to the app, including
the leading x , causing PHP's unserialize() function to choke.
I'm using Drupa
On Thursday, February 24, 2011 3:48:35 pm Adrian Klaver wrote:
> On Thursday, February 24, 2011 3:34:02 pm Aleksey Tsalolikhin wrote:
> > Hi. We're running Postgres 8.4.4 everywhere.
> >
> > I already have a pg_dump -Fc of the big table from the source, now
> > I am running a pg_dump -Fc on the r
On Thursday, February 24, 2011 3:34:02 pm Aleksey Tsalolikhin wrote:
> Hi. We're running Postgres 8.4.4 everywhere.
>
> I already have a pg_dump -Fc of the big table from the source, now
> I am running a pg_dump -Fc on the recipient, to see if the size is
> different.
I thought you already had a
I read in the pg_dump man page that pg_dump does not block other users
accessing the database (readers or writers).
In practice, if I pg_dump our 100 GB database, our application, which
is half Web front end and half OLTP, at a certain point, slows to a
crawl and the Web interface becomes unrespon
Hi. We're running Postgres 8.4.4 everywhere.
I already have a pg_dump -Fc of the big table from the source, now
I am running a pg_dump -Fc on the recipient, to see if the size is different.
Then I will run a pg_dump as text, so I can diff the two files if they are
different in size.
Thanks!!
Al
On Thursday, February 24, 2011 1:11:44 pm Aleksey Tsalolikhin wrote:
> On Thu, Feb 24, 2011 at 11:46 AM, John R Pierce wrote:
> > On 02/24/11 11:02 AM, Aleksey Tsalolikhin wrote:
> >> How do I check the fillfactor on the table, please?
> >
> > its in the field reloptions in pg_class. so...
> >
On Thu, Feb 24, 2011 at 14:11, Aleksey Tsalolikhin
wrote:
>> are you truncating the table before restoring, or is this a restore into a
>> new database, or what?
>
> I've tried both. Slony truncates the table before copying it over, and I've
> tryind pg_restore'ing it into a new database. In bo
Hi,
I have a query using a view such as:
SELECT *
FROM taskretrievalwithfiles
WHERE ti_id='ti_0r0w2';
The view taskretrievalwithfiles is defined as:
SELECT taskinstance.ti_id, lotsofotherstuff
FROM taskinstance
JOIN store ON taskinstance.s_id=store.s_id
JOIN sto
On Thu, Feb 24, 2011 at 11:46 AM, John R Pierce wrote:
> On 02/24/11 11:02 AM, Aleksey Tsalolikhin wrote:
>
>> How do I check the fillfactor on the table, please?
>
> its in the field reloptions in pg_class. so...
>
> select reloptions from pg_class where relname='tablename';
Thanks, John!
Hey,
2011/2/24 akp geek
> Hi all -
>
> I am trying to find the number of elements in the array.
> Right now I am using array_upper and array_lower. Is there any other way of
> getting the number of elements?
>
You may use array_length() function, e.g.
dmitigr=> SELECT array_length(
Yup, that did it. And you're right, you don't need to escape the '.'.
So the extra \ is needed because of the single quotes string. A.
:-)
Thanks Steve !
From: Steve Crawford [mailto:scrawf...@pinpointresearch.com]
Sent: Thursday, February 24, 2011 1:40 PM
To: Gauthier, Dave
Cc
On 02/24/11 11:02 AM, Aleksey Tsalolikhin wrote:
How do I check the fillfactor on the table, please?
its in the field reloptions in pg_class. so...
select reloptions from pg_class where relname='tablename';
if tablename is non-unique, you'll need to qualify that with the OID of
the na
It may help to specify why you feel that array_upper and array_lower are
insufficient for your use. I mean, you could " count( unnest( array ) ) "
but whether that is better or worse than array_upper really depends on your
needs.
David J.
From: pgsql-general-ow...@postgresql.org
[mailto:pg
Hi all -
I am trying to find the number of elements in the array. Right
now I am using array_upper and array_lower. Is there any other way of
getting the number of elements?
thanks for the help
Regards
On Thu, Feb 24, 2011 at 8:02 PM, David Johnston wrote:
> A column constraint can only reference its own column. Since you are
> referencing "completed" in the CHECK it implicitly converts the Column
> constraint into a Table constraint - and table constraints do not reference
> the name of a colu
Hi. Thanks for your replies.
How do I check the fillfactor on the table, please?
(http://www.postgresql.org/docs/8.4/static/sql-createtable.html tells me how
to set it, but I haven't found yet how to read it.)
Same CPU, same filesystem, same blocksize - identical systems. Same model
of server.
A column constraint can only reference its own column. Since you are
referencing "completed" in the CHECK it implicitly converts the Column
constraint into a Table constraint - and table constraints do not reference
the name of a column like a column constraint does during name
auto-generation.
D
On Thu, Feb 24, 2011 at 07:30:32PM +0100, Alexander Farber wrote:
> Shouldn't the line
>
> "pref_match_check" CHECK (completed >= win AND win >= 0)
>
> above actually be:
>
> "pref_match_win_check" CHECK (completed >= win AND win >= 0)
>
> ? Does it indicate something went wrong or is i
On 02/24/2011 10:25 AM, Gauthier, Dave wrote:
select 'abc.def[0]' ~ E'^[a-zA-Z0-9_*\.\[\]*]+$';
Try:
E'^[a-zA-Z0-9._\\[\\]]+$'
The "outer" level of parsing turns that into '^[a-zA-Z0-9._\[\]]+$'
which is the regex you want. Also, I'm *pretty sure* you don't need to
escape the '.' within a ch
Hello,
I have a paranoic question.
In PostgreSQL 8.4.7 I had a table to store started, completed and
interrupted games :
# \d pref_match
Table "public.pref_match"
Column | Type |Modifiers
---+---+-
I want to include '[', ']', and '.' in a list of permitted chars in a regexp.
This doesn's seem to work...
select 'abc.def[0]' ~ E'^[a-zA-Z0-9_\.\[\]]+$';
?collum?
f
(1 row)
Help!
On 24/02/11 15:24, marcin mank wrote:
On Tue, Feb 22, 2011 at 9:44 PM, Aleksey Tsalolikhin
wrote:
Most of our data is in a single table, which on the old server is 50 GB in
size and on the new server is 100 GB in size.
Maybe the table the on new server has fillfactor less than 100 ?
That
Le 24/02/2011 16:49, gvim a écrit :
> PostgreSQL 9.0.3/Mac OS X 10.6.6
>
> I need to recreate a database at regular intervals and what normally
> works is:
>
> user$: dropdb -U myuser -h localhost mydb;
>
> user $: psql -U myuser -h localhost mydb;
> psql: FATAL: database "mydb" does not exist
>
On Feb 24, 2011, at 9:19 PM, gvim wrote:
> Now, for some reason, when I recreate the datbase the old tables are still
> present. I've tried the same procedure using a psql login but the result is
> the same.
This can happen if template1 database has those tables. Check/verify if those
table
PostgreSQL 9.0.3/Mac OS X 10.6.6
I need to recreate a database at regular intervals and what normally works is:
user$: dropdb -U myuser -h localhost mydb;
user $: psql -U myuser -h localhost mydb;
psql: FATAL: database "mydb" does not exist
user$: createdb -U myuser -h localhost mydb;
Now, fo
On Tue, Feb 22, 2011 at 9:44 PM, Aleksey Tsalolikhin
wrote:
> Most of our data is in a single table, which on the old server is 50 GB in
> size and on the new server is 100 GB in size.
>
Maybe the table the on new server has fillfactor less than 100 ?
Greetings
Marcin
--
Sent via pgsql-genera
On 02/24/2011 12:51 AM, Michael Black wrote:
Look at the "Search Filters" and "LDAP URL" sections of
http://quark.humbug.org.au/publications/ldap/ldap_tut.html . There are
some samples of "wildcard" filters there.
I tried a number of possibilities for the ldap url based on the LDAP URL
section
On Wed, Feb 23, 2011 at 9:59 PM, Kalai R wrote:
> Is it possible to maintain in a single database for all years of data?
Yes it is possible. But nobody can answer that for your specific
situation without knowing the amount of data and how you plan to use
it.
--
Sent via pgsql-general mailing l
2011/2/22 Adarsh Sharma :
> Dear all,
>
> Today I need to back up a mysql database and restore in Postgresql database
> but I don't know how to achieve this accurately.
In addition to other suggestions, you could also use open source Tungsten
Replicator which has real-time MySQL to PostgreSQL repl
On 02/23/2011 10:27 PM, Magnus Hagander wrote:
On Wed, Feb 23, 2011 at 11:43, Sim Zacks wrote:
Is there a way to do ldap authentication in pg_hba on a structure that has
multiple ou objects?
Lets say I have an ou=Users and then an ou per dept.
I want the ldap to do authentication no matter w
On Sat, Feb 19, 2011 at 11:31 PM, Brar Piening wrote:
> On Thu, 17 Feb 2011 07:58:46 -0800 (PST), Ahmed
> wrote:
>
>> I tried changing that one line to use UTF-8 encoder, but the password
>> packet
>> didn't get fixed. It works smoothly if kept in byte array instead of
>> string.
>>
> Yes, as SS
Hi Aleksey,
I've read your previous post, and although I'm not quite sure what is the
root cause, I have some questions and (maybe wild) guesses ...
1) Are those two machines (primary and DR) exactly the same? I mean CPU,
filesystem (including blocksize etc.)?
2) What about database encoding? I
Hello,
Exactly, I want to drop unused tables, views, etc..., I am writing now a shell
script to handle this issue by analyzing the log files. If you have suggestions
and comments I will be glade to hear it.
Regards
From: Michael Black
To: s_ju...@yah
On Thu, Feb 24, 2011 at 1:10 AM, Michael Glaesemann wrote:
>
> On Feb 23, 2011, at 13:49, John R Pierce wrote:
>
> > On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
> >>> *3. Start-End IP format :* 1.2.3.0-1.2.3.255
> >> You don't even need to program the conversion, it is already done:
> >>
On Thu, Feb 24, 2011 at 3:03 AM, Tom Lane wrote:
> John R Pierce writes:
> > On 02/23/11 4:44 AM, Stephane Bortzmeyer wrote:
> > *3. Start-End IP format :* 1.2.3.0-1.2.3.255
> >> You don't even need to program the conversion, it is already done:
> >>
> >> % netmask 1.2.3.0:1.2.3.255
> >> 1.
43 matches
Mail list logo
