Re: [GENERAL] issue with lo_lseek - it returns 4
It would be great to remove the limitation. I can image various possibilities if Postgres can handle larger lo objects. For example, to stream HD content from DB to a multimedia device for displaying. Would that be technically hard to do? My impression is that lo has pretty scalable implementation already. On Wed, Jun 17, 2009 at 4:05 PM, Tom Lane wrote: > Konstantin Izmailov writes: > > Out of curiosity, what if lo object has size > 4GB, how lo_tell return > its > > size? Looks like this is an interface issue. > > That's simple: it can't have such a size. > > Allowing LOs bigger than 2GB is on the TODO list, but don't hold your > breath. Most people who are interested in objects that large are > storing them out in the filesystem anyway. > >regards, tom lane >
Re: [GENERAL] Some strange bug with drop table with slony cluster
Scott Marlowe writes: > We've run into issues with drop table and slony as well. What version > of slony are you running? We're running 1.2.14. Latest version in > that branch in 1.2.16, but we haven't had cause to upgrade to it just > yet. I'll be looking at 2.0.latest and 1.2.16 over the summer to see > if one or the other can fix this issue for us. FWIW, I believe one of the design goals for 2.0 was to get rid of their direct manipulation of the system catalogs, which should in turn remove this type of hazard. I don't know the actual status though. 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] Some strange bug with drop table with slony cluster
2009/6/17 Maxim Boguk : > DB version: PostgreSQL 8.3.6 (under linux) > no server/db crashes happen before. > > Server was slave in slony replication. > > Now problem: > table was unsubscribed from replication (without any errors) > and then dropped from master without any errors > > But when i try drop table from slave i got very strange error: We've run into issues with drop table and slony as well. What version of slony are you running? We're running 1.2.14. Latest version in that branch in 1.2.16, but we haven't had cause to upgrade to it just yet. I'll be looking at 2.0.latest and 1.2.16 over the summer to see if one or the other can fix this issue for us. -- 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] issue with lo_lseek - it returns 4
Konstantin Izmailov writes: > Out of curiosity, what if lo object has size > 4GB, how lo_tell return its > size? Looks like this is an interface issue. That's simple: it can't have such a size. Allowing LOs bigger than 2GB is on the TODO list, but don't hold your breath. Most people who are interested in objects that large are storing them out in the filesystem anyway. 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] used for large media files
Steve Atkins wrote: On Jun 17, 2009, at 8:43 AM, Mike Kay wrote: Now that's an interesting way of doing this I never thought about before. Using a fileserver though, how would I categorize and index the files? I was planning on using multiple databases to hold the data - one for each client and a separate database for each file type. Yes, they would be hosted on the same server. I see the bottleneck. I suppose that instead of saving the files, indexes and categories all in the same database, I could simply reference the location and file names in the database - and index and categorize in this manner. Does this make sense? Storing all the metadata in the database and the content on the filesystem of the webserver lets both do what they're good at. Serving static files from the filesystem of the webserver is ridiculously cheap compared with retrieving the data from the database, as it's something that everything from the kernel up is optimized to do. Backups are much simpler too. Using the database to store BLOBs or do it via File system is a very old debate going back and fourth with common tone the db is slower the file system is faster. Using a DB easies maintenance, simplifies indexing, security and gives transaction protection to the files. In my view the only argument still holding water storing large binary files on the Filesystem vs the DB is the overhead/access time losses connecting and read data from DB. The file system just wins out yet has several draw backs. Also consider one does not need to use the large object interface anymore, the bytea type with TOAST simplify that problem . The draw back is you can't jump around the binary stream and the size is limited to 1Gig per record. One of the big draw backs to using File system and a DB for indexing/meta data is keeping the two up to date and linked. If files get accidentally deleted or moved to different directories the database index is now useless. This by itself can cause maintenance nightmare as the number of files and directories get into 10 of thousands. This also complicates disaster recovery because the directory structure has to be recreated exactly to get it to work again. -- 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] Some strange bug with drop table with slony cluster
Maxim Boguk writes: > Server was slave in slony replication. > Now problem: > table was unsubscribed from replication (without any errors) > and then dropped from master without any errors > But when i try drop table from slave i got very strange error: > hh=# drop TABLE metro_station_old; > ERROR: "area_pk" is an index You'd be best off to ask about this on the Slony mailing lists. Slony plays some strange games with the system catalogs, and I think that cases like this are fairly common if you don't perform schema changes on slon-ified tables in just exactly the right way. Hopefully somebody over there will recognize this particular symptom and help you out. 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] how to cancel a query in progress
Whit Armstrong writes: > getting a few "page xxx is uninitialized --- fixing" warnings in the > vacuum output, but seems like this should be expected since I killed > an insert in progress. Yeah, that isn't too surprising. When a table runs out of space the first thing we do is extend it with zeroed pages, mainly to make sure that the disk space is available. The next thing that will hit disk is WAL records about the insertions, and finally the updated pages themselves (overwriting the zeroes). A forced database crash before the WAL gets written leads to some all-zero pages left behind on disk. They're harmless, and VACUUM knows enough to clean them up and make them useful (but it emits a WARNING just to be chatty). 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] issue with lo_lseek - it returns 4
Out of curiosity, what if lo object has size > 4GB, how lo_tell return its size? Looks like this is an interface issue. I found a post dated back to 1998, when somebody pointed it out and a posgres developer promised to fix it. Thank you all On Wed, Jun 17, 2009 at 3:15 PM, Konstantin Izmailov wrote: > I found the reason - it was bug in my code when inserting lo object. It's > size was actually 4. > Please ignore or delete my post. > > On Wed, Jun 17, 2009 at 11:38 AM, Konstantin Izmailov < > kizmai...@gmail.com> wrote: > >> Hi, >> I'm trying to get lo size via libpq before starting reading it (postgres >> server 8.3.5). lo_lseek always returns 4 (actual size is > 1M). >> I query table with "select photo from employee where id=''". It >> returns lo oid integer as expected. Then I use following code to obtain size >> of the lo object: >> int lobj_fd = lo_open(conn, lobjId, INV_READ); // returns valid fd, >> ok >> >> lo_lseek(conn, lobj_fd, 0L, SEEK_END ); >> size = lo_tell(conn, lobj_fd); // always returns 4, error? >> >> Is it possible to obtain lo object size before reading it? >> >> Thank you >> Konstantin >> >> >
Re: [GENERAL] issue with lo_lseek - it returns 4
I found the reason - it was bug in my code when inserting lo object. It's size was actually 4. Please ignore or delete my post. On Wed, Jun 17, 2009 at 11:38 AM, Konstantin Izmailov wrote: > Hi, > I'm trying to get lo size via libpq before starting reading it (postgres > server 8.3.5). lo_lseek always returns 4 (actual size is > 1M). > I query table with "select photo from employee where id=''". It returns > lo oid integer as expected. Then I use following code to obtain size of the > lo object: > int lobj_fd = lo_open(conn, lobjId, INV_READ); // returns valid fd, ok > > lo_lseek(conn, lobj_fd, 0L, SEEK_END ); > size = lo_tell(conn, lobj_fd); // always returns 4, error? > > Is it possible to obtain lo object size before reading it? > > Thank you > Konstantin > >
Re: [GENERAL] GiST or GIN, I feel like I am doing something wrong
Aaron,
did you actually check performance of search in both cases ?
GiST index can be small but very inefficient, since top-level
signatures can be degenerated, so we just remove them. It's easy to
see that looking in explain analyze - see difference between number of
rows found by index and actual number after recheck. And remember, recheck
needs access to the heap, which is slow and can kill performance.
GIN is big, but it should be fine for your setup. Also, see explain analyze.
Oleg
On Wed, 17 Jun 2009, Aaron wrote:
We are testing full text searching on a small chunk of our data. We
have created an INDEX to make searching faster. From the PostgreSQL
8.3 docs, we are running 8.3.7, it seems we should be running GIN
indexes. The reason GIN on paper seems like the right INDEX:
* we have static data
* we have over 241071 unique words (lexemes)
* GIN index lookups are about three times faster and we are 99.9% searching
The problem is that we have been testing with both INDEX types and
GiST is killing GIN. I believe it has to do with the size of our GiST
index.
SELECT * from relation_size where relation like '%full%';
relation | size
+
public.profile_images_fulltext_gin | 437 MB
public.profile_images_fulltext | 161 MB
public.profile_images_fulltext_gist| 66 MB
public.profile_images_fulltext_pif_key_key | 18 MB
(4 rows)
So my questions...
Why is the GiST index so large?
Would the large size likely effect performance?
Am I doing something fundamentally wrong?
Yes I was sure to ANALYZE public.profile_images_fulltext between all
my INDEX DROP and CREATE
More details:
owl=# \d profile_images_fulltext
Table "public.profile_images_fulltext"
Column |Type | Modifiers
--+-+---
pif_key | bigint | not null
content | tsvector|
datetime_created | timestamp without time zone | default now()
raw | text|
owl=# SELECT count (pif_key) from public.profile_images_fulltext;
count
630699
(1 row)
owl=# SELECT count(word) FROM ts_stat('SELECT content FROM
profile_images_fulltext');
count
241071
(1 row)
CREATE INDEX profile_images_fulltext_gin ON profile_images_fulltext
USING gin(content);
CREATE INDEX profile_images_fulltext_gist ON profile_images_fulltext
USING gist(content);
Any and all thoughts would be greatly appreciated,
Aaron Thul
http://www.chasingnuts.com
Life is complex: it has real and imaginary components.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] partitioning example
So, I threw this together last night. It's not pretty, it's not perfect, but it works well enough. There are two files, attached, mkstp and mktrig. Both are php programs. The first, mkstp makes partitions. mkstp checks to see if a partition table already exists, if it does it goes on to the next date, otherwise it creates the new partition and (in this case) creates indexes. You can throw in grants there as well. mktrig prints out the text needed to create the trigger. It requires to be run something like ./mktrig | psql mydb to work. So it's easy to see your trigger creation script. Feel free to make it work just by hitting the db if it helps you. These scripts, in a very similar form are working quite well for me. A third small .sql script is included called moveit. It's designed to move data from the parent table into its children, 10 rows at a time. Suggestions welcome. mktrig Description: Binary data moveit Description: Binary data mkstp Description: Binary data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Some strange bug with drop table with slony cluster
DB version: PostgreSQL 8.3.6 (under linux) no server/db crashes happen before. Server was slave in slony replication. Now problem: table was unsubscribed from replication (without any errors) and then dropped from master without any errors But when i try drop table from slave i got very strange error: hh=# drop TABLE metro_station_old; ERROR: "area_pk" is an index hh=# \d+ metro_station_old Table "public.metro_station_old" Column | Type | Modifiers | Description --+---+---+- metro_station_id | integer | not null | city_id | integer | not null | metro_line_id| integer | not null | city_district_id | integer | not null | name | character varying(64) | not null | image_point_x| integer | | image_point_y| integer | | acronym_line | character varying(10) | default ''::character varying | colocation | integer | | Foreign-key constraints: "metro_station_ibfk_1" FOREIGN KEY (city_id) REFERENCES area(area_id) "metro_station_ibfk_2" FOREIGN KEY (metro_line_id) REFERENCES metro_line(metro_line_id) "metro_station_ibfk_3" FOREIGN KEY (city_district_id) REFERENCES tables_to_drop.city_district(city_district_id) Has OIDs: no hh=# \d+ area_pk Index "public.area_pk" Column | Type | Description -+-+- area_id | integer | primary key, btree, for table "public.area" complete independent things. Also: hh=# ALTER TABLE metro_station_old drop constraint "metro_station_ibfk_1"; ERROR: "area_pk" is an index hh=# ALTER TABLE metro_station_old drop constraint "metro_station_ibfk_2"; ERROR: "metro_line_pk" is an index hh=# ALTER TABLE metro_station_old drop constraint "metro_station_ibfk_3"; ERROR: "city_district_pk" is an index First thing witch i think it is corrupted cache in shared memory or something like... but fresh restart of db server do not change situation. second thing is possible broken system indexes... but reindex system hh; not help too hh=# SELECT oid from pg_class where relname='metro_station_old'; oid --- 17542 (1 row) hh=# SELECT oid from pg_class where relname='area_pk'; oid --- 18933 (1 row) Now i out of ideas. Main thing what made me worry is second slave show same issue. So that is not single random error. Because one of slaves is pure backup i can easy experiment with him. But need any hints what to look next. PS: one additional info... i can rename table via alter without errors. But still can't drop it. -- SY, 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
Re: [GENERAL] Naming functions with reserved words
> Uh, what project is that exactly, and was it even working within Postgres? The project is http://pgfoundry.org/projects/temporal/ But it looks like I'm just stupid or confused (or confused and stupid). I'm working on porting temporal extensions I wrote originally for Oracle to Postgres. When I was half way there, I discovered the temporal project. So I was working on compatibility between the two. All of my functions were prefixed and none of theirs were. So I guess I mistakenly read their functions as intersect() and union(). But going back to the source code, I see that they prefixed those two functions period_intersect() and period_union(). I appologize for being a doofus. Scott
[GENERAL] issue with lo_lseek - it returns 4
Hi, I'm trying to get lo size via libpq before starting reading it (postgres server 8.3.5). lo_lseek always returns 4 (actual size is > 1M). I query table with "select photo from employee where id=''". It returns lo oid integer as expected. Then I use following code to obtain size of the lo object: int lobj_fd = lo_open(conn, lobjId, INV_READ); // returns valid fd, ok lo_lseek(conn, lobj_fd, 0L, SEEK_END ); size = lo_tell(conn, lobj_fd); // always returns 4, error? Is it possible to obtain lo object size before reading it? Thank you Konstantin
Re: [GENERAL] GiST or GIN, I feel like I am doing something wrong
Tom, Our maintenance_work_mem is 1024MB so there should have been plenty of memory for INDEX creation. I happened to be watching top when we created the GiN INDEX and the process used about 500MB of non-shared memory. Aaron Thul http://www.chasingnuts.com On Wed, Jun 17, 2009 at 11:30 AM, Tom Lane wrote: > Aaron writes: >> CREATE INDEX profile_images_fulltext_gin ON profile_images_fulltext >> USING gin(content); >> CREATE INDEX profile_images_fulltext_gist ON profile_images_fulltext >> USING gist(content); > > What did you have maintenance_work_mem set to while you did this? > GIST doesn't care, but GIN likes to have lots of workspace while > building an index. I'm not entirely sure if small workspace only > affects build time, or if it could result in a bloated/inefficient > index ... but if the latter is true it might explain your results. > > 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] how to cancel a query in progress
Thanks. That's a relief. getting a few "page xxx is uninitialized --- fixing" warnings in the vacuum output, but seems like this should be expected since I killed an insert in progress. can anyone confirm that these warnings are ok? WARNING: relation "balances_primary" page 1883404 is uninitialized --- fixing WARNING: relation "balances_primary" page 1883405 is uninitialized --- fixing WARNING: relation "balances_primary" page 1883406 is uninitialized --- fixing -Whit On Wed, Jun 17, 2009 at 12:09 PM, Tom Lane wrote: > Whit Armstrong writes: >> Are there any integrity checks I need to run on the db after this type >> of crash and recovery, or is vacuum --all good enough? > > There isn't anything you need to do. Postgres crashes don't corrupt > on-disk data, as a general rule, and a SIGKILL crash seems particularly > safe from that. > > 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] used for large media files
On Jun 17, 2009, at 8:43 AM, Mike Kay wrote: Now that's an interesting way of doing this I never thought about before. Using a fileserver though, how would I categorize and index the files? I was planning on using multiple databases to hold the data - one for each client and a separate database for each file type. Yes, they would be hosted on the same server. I see the bottleneck. I suppose that instead of saving the files, indexes and categories all in the same database, I could simply reference the location and file names in the database - and index and categorize in this manner. Does this make sense? Storing all the metadata in the database and the content on the filesystem of the webserver lets both do what they're good at. Serving static files from the filesystem of the webserver is ridiculously cheap compared with retrieving the data from the database, as it's something that everything from the kernel up is optimized to do. Backups are much simpler too. All categorization and suchlike is done via the database, with the only time you hit the main filesystem being when you want to serve the media file itself to the user. The only structure you really need for the static files is something that makes accessing them reasonably cheap, which is mostly driven by limiting the number of files or subdirectories in each directory to a reasonable number. The names of the files and the directory structure don't really need to be meaningful. I've done something similar and added some level of transaction safety to removing files by having any deletion handled through the database, using triggers to add the filename of any object that was removed to a queue table. Then an external process polls that queue table and removes the actual file. Cheers, Steve -- 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] how to cancel a query in progress
Whit Armstrong writes: > Are there any integrity checks I need to run on the db after this type > of crash and recovery, or is vacuum --all good enough? There isn't anything you need to do. Postgres crashes don't corrupt on-disk data, as a general rule, and a SIGKILL crash seems particularly safe from that. 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] Playing with set returning functions in SELECT list - behaviour intended?
"Albe Laurenz" writes: > How about my last example that involved aggregate functions, where > I surprisingly got only one result row? Oh, you're right, now that I look closer that one is a bug. Fixed. 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] how to cancel a query in progress
Thanks, Tom. Lesson learned. Are there any integrity checks I need to run on the db after this type of crash and recovery, or is vacuum --all good enough? -Whit On Wed, Jun 17, 2009 at 11:19 AM, Tom Lane wrote: > Whit Armstrong writes: >> I had a few queries that were fired from pgAdmin, but failed to stop >> running after I killed the GUI. > >> I tried to stop the queries by killing the pid (of the process running >> the query, not the pid of the server) from the linux command line, and >> much to my surprise, the whole database went down and then recovered >> (using the data in pg_xlog I assume). > > I suppose you used "kill -9"? Don't do that. > >> So, now that I've learned this lesson, what is the proper way to kill >> a query, and can it be done from psql or are there special postgres >> tools to do this. > > kill -INT is a safe query-cancel method. > > 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] used for large media files
Now that's an interesting way of doing this I never thought about before. Using a fileserver though, how would I categorize and index the files? I was planning on using multiple databases to hold the data - one for each client and a separate database for each file type. Yes, they would be hosted on the same server. I see the bottleneck. I suppose that instead of saving the files, indexes and categories all in the same database, I could simply reference the location and file names in the database - and index and categorize in this manner. Does this make sense? > When this question comes up every now and again (check the archives) > the consensus turns out to be that, yes, Postgres will do this for you > just fine. just so long as you realize that storing big blobs of > unchanging data in any relational database may not be the best use of > a database. > > On the plus side, you know that all your media and all its metadata is > transactionally safe, and that it's all in the same place. On the > negative side, it's all in the same place, which means the database > can become more of a bottleneck than it needs to be. Fileservers are > *cheap* compared to database servers, and scale out much better. > Databases go faster when they don't have to keep track of as much stuff. > > But this is all true of any RDMS. If it's what you want, then Postgres > will do as good a job of it as you're going to find anywhere else. > > On Jun 17, 2009, at 5:12 AM, Mike Kay wrote: > >> Greetings. I am in the process of deciding my infrastruture for a web >> based application dealing with audio, video and image files. In my >> discussions with web developers PostgreSql came up as a candidate >> for my >> database. This is my FIRST introduction to this database, although >> I've >> heard of it - I have no knowledge of using it. >> >> What I am attempting to build is a database driven web site that >> allows >> users to easily upload either audio, video or images of any type - >> categorize the files, then output the files via streaming. I would >> like >> the users to be able to voice annotate images and build presentations. >> >> Would PostgreSQL be a good database for this type of application? >> Database >> size could grow large very quickly. >> >> I have not decided on the rest of my platform yet, for coding the >> interface and presenting it. >> >> Any input most appreciated. >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > The highest achievement possible is compassion. -- 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] GiST or GIN, I feel like I am doing something wrong
Aaron writes: > CREATE INDEX profile_images_fulltext_gin ON profile_images_fulltext > USING gin(content); > CREATE INDEX profile_images_fulltext_gist ON profile_images_fulltext > USING gist(content); What did you have maintenance_work_mem set to while you did this? GIST doesn't care, but GIN likes to have lots of workspace while building an index. I'm not entirely sure if small workspace only affects build time, or if it could result in a bloated/inefficient index ... but if the latter is true it might explain your results. 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] how to cancel a query in progress
Whit Armstrong writes: > I had a few queries that were fired from pgAdmin, but failed to stop > running after I killed the GUI. > I tried to stop the queries by killing the pid (of the process running > the query, not the pid of the server) from the linux command line, and > much to my surprise, the whole database went down and then recovered > (using the data in pg_xlog I assume). I suppose you used "kill -9"? Don't do that. > So, now that I've learned this lesson, what is the proper way to kill > a query, and can it be done from psql or are there special postgres > tools to do this. kill -INT is a safe query-cancel method. 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] Naming functions with reserved words
Scott Bailey writes: > I noticed in the temporal project they used reserved words for their > functions (union, intersect, etc) Uh, what project is that exactly, and was it even working within Postgres? > But when I try to create a function like that I get an error and I have > to quote it both when creating the function and when calling it. The > only difference I can see is they their functions are written in C and > mine are in SQL. But that doesn't make sense why you could do it in one > language and not in another. You can't do it in any language in Postgres. UNION and INTERSECT are fully reserved keywords, and there just isn't any sane way to avoid making them so given the restrictions of a LALR(1) parser. 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] Custom Fields Database Architecture
On Tue, Jun 16, 2009 at 12:50:28PM +0100, Greg Stark wrote: > On Mon, Jun 15, 2009 at 2:04 PM, Gnanam wrote: > > > > I also read some article which talks about the type of patterns: > > 1. Meta-database > > 2. Mutating > > 3. Fixed > > 4. LOB > > > > My question here is, what is the best approach to define the > > architecture for custom fields. Performance should not be > > compromised. > > The reason there are multiple patterns are because the best approach > depends very much on the specifics of your needs. > > For all David's dogma there are use cases where EAV is the best fit. Sure there are, just not until every other option has been exhausted. The amount of maintenance needed for EAV always increases, usually with quite nasty complexity terms, which means you need to budget resources for that maintenance if it turns out you can't do it any other way. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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] Playing with set returning functions in SELECT list - behaviour intended?
Tom Lane wrote: > It's always been that way. The lack of any obviously-sane way to > handle multiple SRFs in a targetlist is exactly why the feature is > looked on with disfavor. It is clear that there is no really good way to handle this. How about my last example that involved aggregate functions, where I surprisingly got only one result row? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Could not reattach to shared memory
I am running postgres 8.3.7 on windows server 2008 SP1. But this error alway occur : FATAL: could not reattach to shared memory (key=260, addr=0240): 487 .Here is my log 2009-06-17 20:45:18 ICT LOG: database system was shut down at 2009-06-17 17:42:45 ICT 2009-06-17 20:45:22 ICT LOG: database system is ready to accept connections 2009-06-17 21:36:45 ICT LOG: loaded library "$libdir/plugins/plugin_debugger.dll" FATAL: could not reattach to shared memory (key=260, addr=0240): 487 2009-06-17 21:36:48 ICT LOG: could not receive data from client: Unknown winsock error 10061 2009-06-17 21:36:48 ICT LOG: unexpected EOF on client connection When i run my application again and again, this error disappear. I have NOD32 and add Postgres\bin\*.* to exclusions of NOD32. My system : Postgres 8.3.7 Windows Server 2008 SP1. Postgres 8.4 beta 1 on other port. Postgres 8.1 on other port. NOD32 Sorry for my English Tuan Hoang Anh hatua...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FYI: Load times for a largish DB in 8.2 vs. 8.3 vs. 8.4
Hi, First, the numbers: PG VersionLoad time pg_database_size autovac -- 8.2.13179 min 92,807,992,820on 8.3.7 180 min 84,048,744,044on (defaults) 8.4b2 206 min 84,028,995,344on (defaults) 8.4b2 183 min 84,028,839,696off The bulk of the data is in 16 tables, each having about 55 million rows of the form (int, int, smallint, smallint, int, int, int). Each table has a single partial index on one of the integer columns. The dump file was 14GB compressed. The loads were all done on the same machine, with the DB going on a pair of SATA drives in a RAID-0 stripe. The machine has 2 non-HT Xeons and 8GB RAM. maintenance_work_mem was set to 512MB in all three cases. -- todd -- 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] Playing with set returning functions in SELECT list - behaviour intended?
David Fetter writes: > On Tue, Jun 16, 2009 at 10:06:54AM -0400, Tom Lane wrote: >> It's always been that way. The lack of any obviously-sane way to >> handle multiple SRFs in a targetlist is exactly why the feature is >> looked on with disfavor. > I must be missing something obvious. Isn't the nested loop thing that > happens with generate_series() pretty sane? You've carefully chosen a case in which the LCM is also the product. Try some other combinations of periods and see if you still think it's sane. 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] Playing with set returning functions in SELECT list - behaviour intended?
On Tue, Jun 16, 2009 at 10:06:54AM -0400, Tom Lane wrote: > "Albe Laurenz" writes: > > So it looks like the number of result rows is the least common > > multiple of the cardinalities of all columns in the select list. > > It's always been that way. The lack of any obviously-sane way to > handle multiple SRFs in a targetlist is exactly why the feature is > looked on with disfavor. I must be missing something obvious. Isn't the nested loop thing that happens with generate_series() pretty sane? SELECT generate_series(1,2) AS i, generate_series(1,3) AS j; i | j ---+--- 1 | 1 2 | 2 1 | 3 2 | 1 1 | 2 2 | 3 (6 rows) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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] Naming functions with reserved words
On Wed, Jun 17, 2009 at 01:46:13AM -0700, Scott Bailey wrote: > I noticed in the temporal project they used reserved words for their > functions (union, intersect, etc) > > But when I try to create a function like that I get an error and I have > to quote it both when creating the function and when calling it. The > only difference I can see is they their functions are written in C and > mine are in SQL. But that doesn't make sense why you could do it in one > language and not in another. > > What am I missing? And if it can be done, is there a strong preference > in the community about whether it should be the reserved word or follow > PostGIS's tack and prepend something to all of the functions like > ts_union and ts_intersect? As you've observed above, it's a really, really bad idea to name any database object with a reserved word. Descriptive names are better. That said, you *can* do it by double-quoting each. SELECT "SELECT" FROM "FROM" WHERE "WHERE" = "="; Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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
[GENERAL] GiST or GIN, I feel like I am doing something wrong
We are testing full text searching on a small chunk of our data. We
have created an INDEX to make searching faster. From the PostgreSQL
8.3 docs, we are running 8.3.7, it seems we should be running GIN
indexes. The reason GIN on paper seems like the right INDEX:
* we have static data
* we have over 241071 unique words (lexemes)
* GIN index lookups are about three times faster and we are 99.9% searching
The problem is that we have been testing with both INDEX types and
GiST is killing GIN. I believe it has to do with the size of our GiST
index.
SELECT * from relation_size where relation like '%full%';
relation | size
+
public.profile_images_fulltext_gin | 437 MB
public.profile_images_fulltext | 161 MB
public.profile_images_fulltext_gist| 66 MB
public.profile_images_fulltext_pif_key_key | 18 MB
(4 rows)
So my questions...
Why is the GiST index so large?
Would the large size likely effect performance?
Am I doing something fundamentally wrong?
Yes I was sure to ANALYZE public.profile_images_fulltext between all
my INDEX DROP and CREATE
More details:
owl=# \d profile_images_fulltext
Table "public.profile_images_fulltext"
Column |Type | Modifiers
--+-+---
pif_key | bigint | not null
content | tsvector|
datetime_created | timestamp without time zone | default now()
raw | text|
owl=# SELECT count (pif_key) from public.profile_images_fulltext;
count
630699
(1 row)
owl=# SELECT count(word) FROM ts_stat('SELECT content FROM
profile_images_fulltext');
count
241071
(1 row)
CREATE INDEX profile_images_fulltext_gin ON profile_images_fulltext
USING gin(content);
CREATE INDEX profile_images_fulltext_gist ON profile_images_fulltext
USING gist(content);
Any and all thoughts would be greatly appreciated,
Aaron Thul
http://www.chasingnuts.com
Life is complex: it has real and imaginary components.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to cancel a query in progress
I had a few queries that were fired from pgAdmin, but failed to stop running after I killed the GUI. I tried to stop the queries by killing the pid (of the process running the query, not the pid of the server) from the linux command line, and much to my surprise, the whole database went down and then recovered (using the data in pg_xlog I assume). So, now that I've learned this lesson, what is the proper way to kill a query, and can it be done from psql or are there special postgres tools to do this. Thanks, Whit -- 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] used for large media files
In response to "Mike Kay" : > Greetings. I am in the process of deciding my infrastruture for a web > based application dealing with audio, video and image files. In my > discussions with web developers PostgreSql came up as a candidate for my > database. This is my FIRST introduction to this database, although I've > heard of it - I have no knowledge of using it. > > What I am attempting to build is a database driven web site that allows > users to easily upload either audio, video or images of any type - > categorize the files, then output the files via streaming. I would like > the users to be able to voice annotate images and build presentations. > > Would PostgreSQL be a good database for this type of application? Database > size could grow large very quickly. Yes. PostgreSQL will handle this application swimmingly. In addition to everything else you'd want in a database, PG has a large object API that allows you to store very large data streams such that you can easily access individual parts of the stream (i.e., you can start accessing a video stream at any point in the stream without having to read through everything before that) http://www.postgresql.org/docs/8.3/static/largeobjects.html -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] used for large media files
Greetings. I am in the process of deciding my infrastruture for a web based application dealing with audio, video and image files. In my discussions with web developers PostgreSql came up as a candidate for my database. This is my FIRST introduction to this database, although I've heard of it - I have no knowledge of using it. What I am attempting to build is a database driven web site that allows users to easily upload either audio, video or images of any type - categorize the files, then output the files via streaming. I would like the users to be able to voice annotate images and build presentations. Would PostgreSQL be a good database for this type of application? Database size could grow large very quickly. I have not decided on the rest of my platform yet, for coding the interface and presenting it. Any input most appreciated. -- 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] simulating high load for vacuum full
In response to Ivan Sergio Borgonovo : > I'm trying to diagnose a problem that happened during vacuum full. What _is_ the problem? > It is a programming problem triggered by some lock, delay whatever, > happening during vacuum. The solution is to fix the lock, delay, or whatever issue. > Making large updates to a bunch of tables is a PITA just to obtain a > slow VACUUM FULL. I don't understand what that sentence is supposed to mean. > Restoring a "fragmented" DB doesn't look as a working strategy. > The restore shouldn't be fragmented. It won't be. > What are the "side effects" of a vacuum full? Index fragmentation. Table locks that block other processes until the vacuum full is complete. Heavy disk activity. > Any cheaper way to cause a heavy vacuum full or just its side > effects? Huh? Are you try to simulate a vacuum full for testing, or are you complaining about the side effects of vacuum full? Quite honestly, I can't figure out what your question is or what you're trying to do. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Data merging problem
subodh chaudhari, 17.06.2009 09:05: Hi , I am working on project which include merging of two databases within postgresql on Linux. I have searched for s/w but didnt get any useful. So if any one having any information about such s/w please help me out. This s/w should have GPL. (Opensource s/w). I want to sync two database i.e. the s/w should merge the databses insuch a way that they sholud have same tables and also same records in table. It should generate script using which I can sync two databases. Why don't you use Slony or some similar replication solution for Postgres? They are mode precisely for that. If you can't (or don't want to) use replication, you might want to have a look at my SQL Workbench/J. It offers two commands that could do what you want: WbSchemaDiff[1] which generates an XML description of the schema differences (which can then be transformed into a SQL script) WbDataDiff[2] can compare the contents (data) of two databases and either run the necessary UPDATE/DELETE/INSERT statements directly or write them as SQL scripts. Especially generating the DELETE http://www.sql-workbench.com Thomas [1] http://www.sql-workbench.net/manual/wb-commands.html#command-schema-diff [2] http://www.sql-workbench.net/manual/wb-commands.html#command-data-diff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Naming functions with reserved words
I noticed in the temporal project they used reserved words for their functions (union, intersect, etc) But when I try to create a function like that I get an error and I have to quote it both when creating the function and when calling it. The only difference I can see is they their functions are written in C and mine are in SQL. But that doesn't make sense why you could do it in one language and not in another. What am I missing? And if it can be done, is there a strong preference in the community about whether it should be the reserved word or follow PostGIS's tack and prepend something to all of the functions like ts_union and ts_intersect? Scott -- 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 server NOT STARTED
Chandra Sekar R wrote: I recently installed PostgreSQL 8.3.7 into my windows xp m/c. When I start PostgreSQL server through My computer>control panel>Administrative tools>services>PostgreSQL server>start NOT STARTED and the following error mesage appeared: "some services started and stoped because of they have no job or performance log" any remedy? first step, check the windows Event Viewer, applications, for any postgres entries. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] simulating high load for vacuum full
I'm trying to diagnose a problem that happened during vacuum full. It is a programming problem triggered by some lock, delay whatever, happening during vacuum. Making large updates to a bunch of tables is a PITA just to obtain a slow VACUUM FULL. Restoring a "fragmented" DB doesn't look as a working strategy. The restore shouldn't be fragmented. What are the "side effects" of a vacuum full? Any cheaper way to cause a heavy vacuum full or just its side effects? thanks -- 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] PostgreSQL server NOT STARTED
Hi All, I recently installed PostgreSQL 8.3.7 into my windows xp m/c. When I start PostgreSQL server through My computer>control panel>Administrative tools>services>PostgreSQL server>start NOT STARTED and the following error mesage appeared: "some services started and stoped because of they have no job or performance log" any remedy? thanks chandrasekar
Re: [GENERAL] Dynamic table
> Your problem is currently sounding very much like an exam question; you > seem to be arbitrarily making decisions without showing any real data. > When you deal with real problems in the real world you're normally > making compromises when you model things and hence the decisions > wouldn't be as forced as you're making it. Design is about picking and > choosing between compromises and without knowing what the choices are > you can't design anything, this is one of the problems with tests. I wish it was just an exam question Show real data? Well here it is: customer | value1 | value2 | value3| 1 5 3 4 2 8 2 10 I hope you can believe me when I say that the names value1,value2,value3 really are impossible to relate. I will not decide upon those. Someone else is going to do that in the future. I just have to make a structure that can handle all cases from "shoe size" to "number of atoms in persons body" (well, perhaps I can say that the values will be in the 0...100 range, but that's about it. There is really nothing else I know about how the system will be used by other people. :-( I just know that I have to make them enter data like this since it is a third party that need this kind of data. yes I took some random values for the "real data" since I don't know anything else about the data. -- 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] Data merging problem
subodh chaudhari wrote: Hi , I am working on project which include merging of two databases within postgresql on Linux. I have searched for s/w but didnt get any useful. So if any one having any information about such s/w please help me out. This s/w should have GPL. (Opensource s/w). I want to sync two database i.e. the s/w should merge the databses insuch a way that they sholud have same tables and also same records in table. It should generate script using which I can sync two databases. and how should this free program it handle little things like constraint violations caused by duplicate primary keys, and so forth? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Data merging problem
Hi , I am working on project which include merging of two databases within postgresql on Linux. I have searched for s/w but didnt get any useful. So if any one having any information about such s/w please help me out. This s/w should have GPL. (Opensource s/w). I want to sync two database i.e. the s/w should merge the databses insuch a way that they sholud have same tables and also same records in table. It should generate script using which I can sync two databases. -- Thanks, Subodh Chaudhari
