Re: [GENERAL] Ranking?
On Fri, Aug 22, 2008 at 02:30:46PM -0700, Ruben Gouveia wrote: > Since there is no current solution to ranking values in pl/pgsql as > the rank() over (partition by... that there is in oracle i am hoping > someone can help me out here. It seems likely that the windowing functions patch will get into 8.4, so don't put too, too much effort into this. :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
On Wed, Aug 20, 2008 at 11:25 PM, Dale <[EMAIL PROTECTED]> wrote: > This afternoon I'm > starting to notice that pgAdmin III for Windows sometimes gives > erratic error messages when there is no error or the error is actually > different. I know this because when I recompile after moving the > cursor, the messages change. Maybe my issue was created by pgAdmin > rather than PostgreSQL. Glad you got your problem resolved. I have to admit I tend to mostly use psql to interact with postgres. Nothing against PGAdmin III, it's good software. I just tend to feel more comfy scripting things than clicking things. Whether you're using pgadmin III or psql, it's important that you have the right version. For pgadmin it's usually best to always have the latest version, and with psql the one that matches your db. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function SETOF return type with variable columns?
Merlin Moncure wrote: On Wed, Aug 20, 2008 at 12:59 PM, James Neff <[EMAIL PROTECTED]> wrote: Greetings, Is it possible to have a function with a return type of SETOF that has variable number of return columns? On Wed, Aug 20, 2008 at 10:08 PM, Tom Lane <[EMAIL PROTECTED]> wrote: "Merlin Moncure" <[EMAIL PROTECTED]> writes: PostgreSQL functions are for the most part strictly bound to their return type. There is, however, the trick of declaring the function as "returns record" and then specifying the names and types of the output columns in the calling query. I'm not sure how practical that is to use with a plpgsql function, and in any case it's not the syntax the OP asked for; but it seems worth mentioning in this thread. Here's another approach, using a refcursor: This is cheating according to the rules set by the OP, but it's a great way to provide a flexible way to return data from the database via a single function. create or replace function doit() returs refcursor as $$ declare r refcursor value 'result'; begin /* some query that puts data in refcursor */ end; $$ language plpgsql; -- from psql/app begin; select doit(); fetch all from result; commit; Thanks everyone for the input. I actually decided to build an XML fragment inside my stored proc and return it as a single text field. The calling process then uses XSL to transform the XML to the html table the user needs. Hopefully your responses will help someone else who might have the same question in the future. --Jim
[GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
On Aug 20, 3:02 pm, [EMAIL PROTECTED] ("Scott Marlowe") wrote:
> On Tue, Aug 19, 2008 at 9:51 PM, Dale Harris <[EMAIL PROTECTED]> wrote:
> > As per the original message:
> >>UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" =
> >> Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" =
> >> inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inRelatedID,
> >> "RelatedID") RETURNING "Default" INTO oldDefault;
>
> This is called a code fragment. What people want to see here is a
> self-contained example of it failing. Until you post one of those, no
> one can troubleshoot it because it WORKS FOR THEM.
>
> Create a test table
> insert some data
> create a plpgsql function
> call that function and have it throw an error.
>
> Post all of that here.
>
> --
> Sent via pgsql-general mailing list ([EMAIL PROTECTED])
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-general
I'm sorry about annoying everyone with this issue. I have just tried
to create a test table and function to demonstrate my issue, but all
now appears to be working. I put the original code back into the
function (including the spelling mistake) and it all compiles now
without any errors. I'm only new to PostreSQL and I'm finding it very
good, especially being fully open source and free. This afternoon I'm
starting to notice that pgAdmin III for Windows sometimes gives
erratic error messages when there is no error or the error is actually
different. I know this because when I recompile after moving the
cursor, the messages change. Maybe my issue was created by pgAdmin
rather than PostgreSQL.
Regards,
Dale.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can´t write nothing in \bin folder in windows
Hi all, well, it´s just a windows xp professional file permisions problem, but some other folk had the same problem so... i´m trying to upgrade my postgresql version ( it was 8.2) in windows , so i' ve downloaded and installed postgresql 8.3.3.1 When i try to install postgis 1.3.3 with the Application Stack Builder and after downloading the postgis installer, appears an error "Error opening file for writing" everytime it wants to copy a file to the install_folder. It seems a problem in folder permissions. Windows has a strange behaviour with some folders. They appear as read only and you can´t change that, but it´s not a problem when you want to copy something into it or delete it. For example, i can copy into postgreSQL 8.2 folder and delete this folder. But when i install 8.3 i can´t copy anything into its \bin folder ( for example) and i can delete all folders. i can´t found any info in Inet, so perhaps there is a problem with my computer... Any examples of successfull installation will be welcomed ! :) maybe something has changed in postgresql installation method? Thanks in advance! Gus
[GENERAL] Ranking?
Since there is no current solution to ranking values in pl/pgsql as the rank() over (partition by... that there is in oracle i am hoping someone can help me out here. The table i created contains the following columns: rank | value I wrote a cursor that will give me all the values. rank | value | 100 | 300 | 200 Now, how do i create a FOR LOOP that will populate the rank column of this table given the results of the cursor. Ideally, i would want the FOR LOOP too look at the results of the query results of my cursor and populate each row accordingly and my insert statement would then populate the table and it will look like this rank | value 1 | 300 2 | 200 3 | 100 so in simple terms, i guess i need somekind of iteration based on the values... any examples out there? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installing Postgre 8.3 with a command
Thanks Man!!! I'll try and reply if i have any problems!! On Fri, Aug 22, 2008 at 6:02 PM, Thomas Kellerer <[EMAIL PROTECTED]> wrote: > Anderson dos Santos Donda wrote on 22.08.2008 22:53: > >> Hi all !! >> On Windows, we can install the postgre 8.3 with a command, without >> "windows setup" to install it, like on linux? >> >> > http://pginstaller.projects.postgresql.org/silent.html > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Installing Postgre 8.3 with a command
Anderson dos Santos Donda wrote on 22.08.2008 22:53: Hi all !! On Windows, we can install the postgre 8.3 with a command, without "windows setup" to install it, like on linux? http://pginstaller.projects.postgresql.org/silent.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing Postgre 8.3 with a command
Hi all !! On Windows, we can install the postgre 8.3 with a command, without "windows setup" to install it, like on linux?
Re: [GENERAL] [ADMIN] Auto recovery feature
Postgres, like any descent RDBMS, performs an auto instance crash recovery using its transaction logs. Regards, Husam From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of c k Sent: Friday, August 22, 2008 11:02 AM To: pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: [ADMIN] Auto recovery feature Hello all, As I know and have seen that innodb engine of MySQL does an auto-recovery when MySQL server is crashed or due to power failure. What is with postgreSQL? Does it makes an auto-recovery? and how? Thanks CPK ** This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag **
Re: [GENERAL] [ADMIN] Auto recovery feature
Thanks CPK On Fri, Aug 22, 2008 at 11:36 PM, Tomeh, Husam <[EMAIL PROTECTED]>wrote: > Postgres, like any descent RDBMS, performs an auto instance crash > recovery using its transaction logs. > > > > Regards, > > Husam > -- > > *From:* [EMAIL PROTECTED] [mailto: > [EMAIL PROTECTED] *On Behalf Of *c k > *Sent:* Friday, August 22, 2008 11:02 AM > *To:* pgsql-general@postgresql.org; [EMAIL PROTECTED] > *Subject:* [ADMIN] Auto recovery feature > > > > Hello all, > As I know and have seen that innodb engine of MySQL does an auto-recovery > when MySQL server is crashed or due to power failure. What is with > postgreSQL? Does it makes an auto-recovery? and how? > > Thanks > CPK > > ** > This message contains confidential information intended only for the use of > the addressee(s) named above and may contain information that is legally > privileged. If you are not the addressee, or the person responsible for > delivering it to the addressee, you are hereby notified that reading, > disseminating, distributing or copying this message is strictly prohibited. > If you have received this message by mistake, please immediately notify us > by replying to the message and delete the original message immediately > thereafter. > > Thank you. > > > FADLD Tag > ** >
[GENERAL] Auto recovery feature
Hello all, As I know and have seen that innodb engine of MySQL does an auto-recovery when MySQL server is crashed or due to power failure. What is with postgreSQL? Does it makes an auto-recovery? and how? Thanks CPK
Re: [GENERAL] AT TIME ZONE and DST in UTC<->CET conversion
Tom Lane wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: > > ISTM this is the one that's wrong. "CET" is standard time, it, GMT+1. > > > If you want a timezone which switches between CET and CST automatically you > > should use something like Europe/Paris. > > Well, actually he *is* using such a zone: > > regression=# select * from pg_timezone_names where name = 'CET'; > name | abbrev | utc_offset | is_dst > --+++ > CET | CEST | 02:00:00 | t > (1 row) > > But > > regression=# select * from pg_timezone_abbrevs where abbrev = 'CET'; > abbrev | utc_offset | is_dst > ++ > CET| 01:00:00 | f > (1 row) > > The problem is that one of these two statements is using the abbrev > meaning and the other is using the timezone meaning. > > We don't have much control over the zone definition, so I'm thinking > maybe the abbrev should be removed from the tznames lists. But that > seems a bit sucky too. Does anyone have any idea if the zic folk would > be responsive to a complaint that defining a timezone with the same > name as an abbreviation is a bad idea? Is this a TODO? -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Single character bitfields
On Aug 21, 2008, at 3:45 AM, Tomasz Ostrowski wrote: - one byte "char" (with quotes), but it is a non standard, integral type, will cause interface problems and I don't know if it will not be deprecated some time. It's used in the catalogs, so I'd say the odds of it getting replaced anytime in the near future (if ever) are next to zero, especially considering "it ain't broke, so don't fix it" and that there's undoubtedly people using it in the wild. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] problem with foreign keys + data-only backup
Markus Mehrwald wrote: > Hi, > > I do not think this will work. As far as I know foreign key checks are > not triggers. Postgres is very strict with things concerning referential > integrity so you cannot turn them off. Actually you are mistaken -- FKs are triggers, and you can turn them off. There is a reason data-only backups are not recommended. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql 8.3 statistic stadistinct value dead wrong even with default_statistics_target=1000
Maxim Boguk <[EMAIL PROTECTED]> writes: > [ ndistinct estimates way off ] Estimating the number of distinct values in a distribution with a long tail is just a really hard problem :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SERIAL datatype
On Thu, Aug 21, 2008 at 09:08:24PM +0200, Peter Billen wrote: > Is it possible to insert a new entry? Will the serial sequence somehow > be able to find the gap (3)? As others have said, no it's not going to. Sequences will only return values out of order when explicitly told to. The main reason is to help prevent ambiguities in the data; if it could automatically reset it would be much more difficult to determine if 7 was "older" than 3 or, worse, which 7 is the correct one. In a well designed system this shouldn't ever occur, but if something does go horribly wrong it's much easier to put the pieces back together this way. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with foreign keys + data-only backup
Hi, I do not think this will work. As far as I know foreign key checks are not triggers. Postgres is very strict with things concerning referential integrity so you cannot turn them off. Maybe it will help to use "--orig-order" for creating the dump. Markus Richard Huxton schrieb: > Peter Billen wrote: >> Hi all, >> >> I have a problem with foreign keys and data-only (no schema) backup. I >> have a simple table node (pseudo-SQL): > >> When I do a data-only backup, the backup file contains following two lines: >> >> INSERT INTO NODE (node_id, parent_node_id) VALUES (2, 1); >> INSERT INTO NODE (node_id, parent_node_id) VALUES (1, NULL); >> >> Restoring the backup file into another database ofcourse fails, because >> the parent_node_id (1) in the first INSERT statement refers to an >> unknown (to be added) node (in the second statement). >> >> How do I make sure my backup orders the insert statements in a logical >> order? > > You don't. You might find --disable-triggers useful though. See the > pg_restore documentation for details. > -- Dipl.-Inform. Med. Markus Mehrwald Institut für Prozessrechentechnik, Automation und Robotik Medizin-Gruppe Universität Karlsruhe (TH) Gebäude 40.28, Zimmer 110 Engler-Bunte-Ring 8 76131 Karlsruhe Fon: +49 (721) 608-7113 Fax: +49 (721) 608-7141 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with foreign keys + data-only backup
Peter Billen wrote: > Hi all, > > I have a problem with foreign keys and data-only (no schema) backup. I > have a simple table node (pseudo-SQL): > When I do a data-only backup, the backup file contains following two lines: > > INSERT INTO NODE (node_id, parent_node_id) VALUES (2, 1); > INSERT INTO NODE (node_id, parent_node_id) VALUES (1, NULL); > > Restoring the backup file into another database ofcourse fails, because > the parent_node_id (1) in the first INSERT statement refers to an > unknown (to be added) node (in the second statement). > > How do I make sure my backup orders the insert statements in a logical > order? You don't. You might find --disable-triggers useful though. See the pg_restore documentation for details. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LIKE and SIMILAR TO
On Fri, 22 Aug 2008 16:43:47 +0530 "c k" <[EMAIL PROTECTED]> wrote: > Hello all, > As we are migrating our ERP application from MySQL to PostgreSQL > we have some difficulties. One of them is use of Like and Similar > to operators. We often use LIKE to search a string from front-end > without case sensetivity. As postgreSQL's LIKE is case sensitive, > we tried ILIKE and SIMILAR TO, but both are slower than LIKE and > we must need case insensitivity. How can we get this by increases > speed. All search columns are VARCHAR(100)to VARCHAR(250). > Currently without index. I was going to suggest to create a functional index create index on sometable using btree (upper(somecolumn)); select * from sometable where upper(somecolumn) like '%' || upper(somestring) || '%'; but indexes aren't going to work if you're searching with a prefixed %. I was wondering what are the performances of postgresql vs. MySQL in such case. Anyway I'd tweak postgresql.conf before complaining it is slower than MySQL. I've been surprised as well at how postgresql can be fast. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LIKE and SIMILAR TO
Thanks to all, I will try to use tsearch2 with some other index and then reply. Regards, CPK On Fri, Aug 22, 2008 at 5:01 PM, Tino Wildenhain <[EMAIL PROTECTED]> wrote: > Hi, > > c k wrote: > >> Hello all, >> As we are migrating our ERP application from MySQL to PostgreSQL we have >> some difficulties. One of them is use of Like and Similar to operators. We >> often use LIKE to search a string from front-end without case sensetivity. >> As postgreSQL's LIKE is case sensitive, we tried ILIKE and SIMILAR TO, but >> both are slower than LIKE and we must need case insensitivity. How can we >> get this by increases speed. All search columns are VARCHAR(100)to >> VARCHAR(250). Currently without index. >> > > You could build an index on lower(column) and use lower(column) like ... > this would speed up queries with exact match as well as 'foo%' > e.g. "start with..." match. > > Make sure when you create the database cluster (initdb) you > used the currect locale, otherwise lower() (and ilike) probably > do not work as you might expect. > > For any more complex searches I'd recommend full text index, > for example tsearch2. > > Regards > Tino >
Re: [GENERAL] LIKE and SIMILAR TO
Hi, c k wrote: Hello all, As we are migrating our ERP application from MySQL to PostgreSQL we have some difficulties. One of them is use of Like and Similar to operators. We often use LIKE to search a string from front-end without case sensetivity. As postgreSQL's LIKE is case sensitive, we tried ILIKE and SIMILAR TO, but both are slower than LIKE and we must need case insensitivity. How can we get this by increases speed. All search columns are VARCHAR(100)to VARCHAR(250). Currently without index. You could build an index on lower(column) and use lower(column) like ... this would speed up queries with exact match as well as 'foo%' e.g. "start with..." match. Make sure when you create the database cluster (initdb) you used the currect locale, otherwise lower() (and ilike) probably do not work as you might expect. For any more complex searches I'd recommend full text index, for example tsearch2. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Script to export all privileges to csv or similar
On Friday 22 August 2008 05:15, Anton Melser wrote: > Hi, > Does anyone know of a script/tool that allows one to export all users > with all privileges? I realise I could construct a query to do it but > google turned up nothing and if someone else has done the good work... > Cheers > Anton > > -- > echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc > This will help you for 99.9% of your problems ... Have you looked at pg_dumpall -g ? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LIKE and SIMILAR TO
Maybe you should give tsearch2 a try and create a GIN index. Regex searches are sometimes a option. Robert c k wrote: Hello all, As we are migrating our ERP application from MySQL to PostgreSQL we have some difficulties. One of them is use of Like and Similar to operators. We often use LIKE to search a string from front-end without case sensetivity. As postgreSQL's LIKE is case sensitive, we tried ILIKE and SIMILAR TO, but both are slower than LIKE and we must need case insensitivity. How can we get this by increases speed. All search columns are VARCHAR(100)to VARCHAR(250). Currently without index. Thanks, CPK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LIKE and SIMILAR TO
Hello all, As we are migrating our ERP application from MySQL to PostgreSQL we have some difficulties. One of them is use of Like and Similar to operators. We often use LIKE to search a string from front-end without case sensetivity. As postgreSQL's LIKE is case sensitive, we tried ILIKE and SIMILAR TO, but both are slower than LIKE and we must need case insensitivity. How can we get this by increases speed. All search columns are VARCHAR(100)to VARCHAR(250). Currently without index. Thanks, CPK
[GENERAL] Postgresql 8.3 statistic stadistinct value dead wrong even with default_statistics_target=1000
Some time ago i found one simple sql over large table eat whole ram+swap and
almost killed server (postgresql 8.3.3 on 4gb freebsd server):
After some exploring i found what happens:
Query was over simple table:
profiles=# \d+ counter_vis
Table "counter_vis"
Column| Type | Modifiers | Description
--+-+---+-
counter_id | bigint | not null |
visitor_id | bigint | not null |
ts | bigint | not null |
sessionstart | bigint | not null |
sessionend | bigint | not null |
numpageviews | integer | not null |
Indexes:
"counter_vis_counter" btree (counter_id)
"counter_vis_vis" btree (visitor_id)
Has OIDs: no
Which contain around 648M entries. (according fresh analyzed stats from
pg_stat_user_tables).
Query was: select count(*) from (select visitor_id, sum(numpageviews) as s from
counter_vis group by visitor_id having sum(numpageviews)>1) as foo;
With plan:
QUERY PLAN
---
Aggregate (cost=17429989.40..17429989.41 rows=1 width=0)
-> HashAggregate (cost=17422096.40..17426700.65 rows=263100 width=12)
Filter: (sum(counter_vis.numpageviews) > 1)
-> Seq Scan on counter_vis (cost=0.00..12554826.80 rows=648969280
width=12)
Plan look ok... but how query eat over 4gb ram?
After lookin i found one strange point: " rows=263100" because i know in table
must have around 70M unique visitor_id's.
Manual analyze on table do not changed plan.
Here is pg_statistic entry after analyze (with default_statistics_target=10):
profiles=# SELECT * from pg_statistic where starelid=25488 and staattnum=2
order by 2 asc;
-[ RECORD 1
]---
starelid| 25488
staattnum | 2
stanullfrac | 0
stawidth| 8
stadistinct | 263100 ( here is 70M distinct values in
reality)
stakind1| 1
stakind2| 2
stakind3| 3
stakind4| 0
staop1 | 410
staop2 | 412
staop3 | 412
staop4 | 0
stanumbers1 |
{0.00067,0.00067,0.00067,0.00067,0.00067,0.00067,0.00067,0.00067,0.00067,0.00067}
stanumbers2 |
stanumbers3 | {-0.0443004}
stanumbers4 |
stavalues1 |
{413866965,489514660,624858316,753063164,790095243,1279713644,1628857812,2104294292,2726728837,2771123172}
stavalues2 |
{-9035671468843485583,184524075,555699387,921684844,1329929495,1897558820,2602632340,3083805588,3511488708,3963719633,9173061477162286552}
stavalues3 |
stavalues4 |
so stadistinct is 300 times wrong from reality.
Already strange... and i need run that query anyway... so i changed
default_statistics_target to 1000 and analyzed table again:
profiles=# ANALYZE verbose counter_vis;
INFO: analyzing "counter_vis"
INFO: "counter_vis": scanned 30 of 6065134 pages, containing 3210 live
rows and 0 dead rows; 30 rows in sample, 648969338 estimated total rows
ANALYZE
After statistic was better:
starelid| 25488
staattnum | 2
stanullfrac | 0
stawidth| 8
stadistinct | 7.12958e+06
stakind1| 1
stakind2| 2
stakind3| 3
stakind4| 0
staop1 | 410
staop2 | 412
staop3 | 412
staop4 | 0
... long stats skipped...
but stadistinct still 10 times wrong from reality:
profiles=# SELECT count(distinct visitor_id) from counter_vis;
count
--
69573318
(1 row)
Any way deal with such situations? Because 10х difference if postgres choose
hashed plan will easy kill server because OOM (because query will use 10x more
ram then postgres awaited).
Probably some strange effects in statdistinc count algoritm?
Or just any way remove limits on default_statistics_target?
Thanks for help.
PS: sorry for bad english.
--
Maxim Boguk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem with foreign keys + data-only backup
Hi all, I have a problem with foreign keys and data-only (no schema) backup. I have a simple table node (pseudo-SQL): node ( integer node_idNOT NULLPRIMARY KEY; integer parent_node_id NULL; ) It contains the following two entries: node(1, NULL) the rood node(2, 1) a child of the root When I do a data-only backup, the backup file contains following two lines: INSERT INTO NODE (node_id, parent_node_id) VALUES (2, 1); INSERT INTO NODE (node_id, parent_node_id) VALUES (1, NULL); Restoring the backup file into another database ofcourse fails, because the parent_node_id (1) in the first INSERT statement refers to an unknown (to be added) node (in the second statement). How do I make sure my backup orders the insert statements in a logical order? This is how I make the backup: pg_dump.exe -h ... -p 5432 -U ... --column-inserts --ignore-version --file=dump --format=t --data-only --verbose db This is how I import the backup: pg_restore.exe -h -p 5432 -U ... --dbname db --format=t --verbose --table=channel dump Thanks all. Kind regards, Peter -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Script to export all privileges to csv or similar
Hi, Does anyone know of a script/tool that allows one to export all users with all privileges? I realise I could construct a query to do it but google turned up nothing and if someone else has done the good work... Cheers Anton -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] seq bug 2073 and time machine
I was trying to drop a serial. Dropped the default for a column. Now it seems I can't drop the sequence since I incurred in: http://archives.postgresql.org/pgsql-bugs/2005-11/msg00304.php Is there a way I can still delete the sequence without using a backup? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] on delete cascade slowing down delete
On Fri, 22 Aug 2008 08:48:30 +0200 Alban Hertroys <[EMAIL PROTECTED]> wrote: > > > Is it going to make things faster if I: > > > > delete from s; > > reindex table s; > Why do you think this step would help you any? There's no index on > p to begin with. You'd just be reindexing the auto-generated > unique index on s (due to it being a PK). Sorry I forgot to add the index in the example. What if there was an index in s.pid too? But mostly... if I delete s will the deletion of p be faster? > > delete from p; > And no, this would most likely be slower. Why? Stopping a delete from p; I can see that actually postgresql is also executing a delete from s where pid=$1; if s is already empty, and there are no other cascading delete on s, the lookup should be faster. I was wondering if that doesn't make a difference in terms of performance if a) I've an index on pid on both tables or b) s is already empty and... should I reindex s if I "delete from s" first if I want some speed up on delete from p; Anyway this looks more and more a dead end once things get more and more complicated since it requires too much bookkeeping. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hi 5 mess...
Hello, I'm sorry about that email you received earlier about hi5... it was a mess (I've sent it to my whole gmail contact list :-s). Please forget about it. All apologize. andy petrella
