[GENERAL] 8.1.11 PREPARE problem?
This is with jdbc3-415. None of the (jdbc, or pg) change logs since then have mentioned this problem. When run as a prepared statement the first statement will execute and return results, while the next two seem to execute, but return no results. When run by hand, not prepared , each statement runs just fine. when using an older version of the jdbc driver (7.3?), that does not call PREPARE, things work just fine. Looking at the logs below, it sure looks like the statements are running, given the different durations, which are correct for the differences between the statements. help please. Dec 18 10:59:15 devel postgres[6889]: [2-1] LOG: duration: 358.005 ms statement: EXECUTE [PREPARE:select aggregationvalue.value as Dec 18 10:59:15 devel postgres[6889]: [2-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.count as Dec 18 10:59:15 devel postgres[6889]: [2-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttime as Dec 18 10:59:15 devel postgres[6889]: [2-4] aggregationvalue$starttime from aggregationvalue where date_trunc($1, aggregationvalue.stoptime) between $2 and $3 and Dec 18 10:59:15 devel postgres[6889]: [2-5] aggregationvalue.aggregatetype = $4 and split_part(aggregationvalue.value,':',1) = $5 and Dec 18 10:59:15 devel postgres[6889]: [2-6] split_part(aggregationvalue.value,':',2) like $6 and split_part(aggregationvalue.value,':',3) like $7 ] Dec 18 10:59:25 devel postgres[6889]: [4-1] LOG: duration: 25.001 ms statement: EXECUTE [PREPARE:select aggregationvalue.value as Dec 18 10:59:25 devel postgres[6889]: [4-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.count as Dec 18 10:59:25 devel postgres[6889]: [4-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttime as Dec 18 10:59:25 devel postgres[6889]: [4-4] aggregationvalue$starttime from aggregationvalue where date_trunc($1, aggregationvalue.stoptime) between $2 and $3 and Dec 18 10:59:25 devel postgres[6889]: [4-5] aggregationvalue.aggregatetype = $4 and split_part(aggregationvalue.value,':',1) = $5 and Dec 18 10:59:25 devel postgres[6889]: [4-6] split_part(aggregationvalue.value,':',2) like $6 and split_part(aggregationvalue.value,':',3) like $7 ] Dec 18 10:59:25 devel postgres[6889]: [6-1] LOG: duration: 2.000 ms statement: EXECUTE [PREPARE:select aggregationvalue.value as Dec 18 10:59:25 devel postgres[6889]: [6-2] aggregationvalue$value,aggregationvalue.aggregatetype as aggregationvalue$aggregatetype,aggregationvalue.count as Dec 18 10:59:25 devel postgres[6889]: [6-3] aggregationvalue$count,aggregationvalue.stoptime as aggregationvalue$stoptime,aggregationvalue.starttime as Dec 18 10:59:25 devel postgres[6889]: [6-4] aggregationvalue$starttime from aggregationvalue where date_trunc($1, aggregationvalue.stoptime) between $2 and $3 and Dec 18 10:59:25 devel postgres[6889]: [6-5] aggregationvalue.aggregatetype = $4 and split_part(aggregationvalue.value,':',1) = $5 and Dec 18 10:59:25 devel postgres[6889]: [6-6] split_part(aggregationvalue.value,':',2) like $6 and split_part(aggregationvalue.value,':',3) like $7 ] -- 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] 8.1.11 PREPARE problem?
moving on:
After digging through the JDBC source. It seems that the server is
returning a 'C': // Command Status (end of Execute) before returning
any data rows. So it doesn't look like JDBC is causing any problems.
I've tried not running the first, and only running the second, and I get
still no results. But as usual when running by hand, I do get results.
the SQL that i'm running ends up looking like these when run by hand:
1) works as a prepared statement
select aggregationvalue.value as aggregationvalue
$value,aggregationvalue.aggregatetype as aggregationvalue
$aggregatetype,aggregationvalue.count as aggregationvalue
$count,aggregationvalue.stoptime as aggregationvalue
$stoptime,aggregationvalue.starttime as aggregationvalue$starttime from
aggregationvalue where date_trunc('month', aggregationvalue.stoptime)
between '2007-11-01' and '2008-12-01' and
aggregationvalue.aggregatetype = 'MONTHLY' and
split_part(aggregationvalue.value,':',1) = 'JUDICI_PAGE' and
split_part(aggregationvalue.value,':',2) like '%' and
split_part(aggregationvalue.value,':',3) like '%' ;
2) does not work as prepared statement
select aggregationvalue.value as aggregationvalue
$value,aggregationvalue.aggregatetype as aggregationvalue
$aggregatetype,aggregationvalue.count as aggregationvalue
$count,aggregationvalue.stoptime as aggregationvalue
$stoptime,aggregationvalue.starttime as aggregationvalue$starttime from
aggregationvalue where date_trunc('day', aggregationvalue.stoptime)
between '2008-12-18' and '2008-12-18' and
aggregationvalue.aggregatetype = 'HOURLY' and
split_part(aggregationvalue.value,':',1) = 'JUDICI_PAGE' and
split_part(aggregationvalue.value,':',2) like '%' and
split_part(aggregationvalue.value,':',3) like '%' ;
On Thu, 2008-12-18 at 11:21 -0600, Jeremiah Jahn wrote:
> This is with jdbc3-415. None of the (jdbc, or pg) change logs since then
> have mentioned this problem. When run as a prepared statement the first
> statement will execute and return results, while the next two seem to
> execute, but return no results. When run by hand, not prepared , each
> statement runs just fine. when using an older version of the jdbc driver
> (7.3?), that does not call PREPARE, things work just fine. Looking at
> the logs below, it sure looks like the statements are running, given the
> different durations, which are correct for the differences between the
> statements. help please.
>
>
>
>
> Dec 18 10:59:15 devel postgres[6889]: [2-1] LOG: duration: 358.005 ms
> statement: EXECUTE [PREPARE:select aggregationvalue.value as
> Dec 18 10:59:15 devel postgres[6889]: [2-2]
> aggregationvalue$value,aggregationvalue.aggregatetype as
> aggregationvalue$aggregatetype,aggregationvalue.count as
> Dec 18 10:59:15 devel postgres[6889]: [2-3]
> aggregationvalue$count,aggregationvalue.stoptime as
> aggregationvalue$stoptime,aggregationvalue.starttime as
> Dec 18 10:59:15 devel postgres[6889]: [2-4] aggregationvalue$starttime from
> aggregationvalue where date_trunc($1, aggregationvalue.stoptime) between $2
> and $3 and
> Dec 18 10:59:15 devel postgres[6889]: [2-5] aggregationvalue.aggregatetype =
> $4 and split_part(aggregationvalue.value,':',1) = $5 and
> Dec 18 10:59:15 devel postgres[6889]: [2-6]
> split_part(aggregationvalue.value,':',2) like $6 and
> split_part(aggregationvalue.value,':',3) like $7 ]
>
> Dec 18 10:59:25 devel postgres[6889]: [4-1] LOG: duration: 25.001 ms
> statement: EXECUTE [PREPARE:select aggregationvalue.value as
> Dec 18 10:59:25 devel postgres[6889]: [4-2]
> aggregationvalue$value,aggregationvalue.aggregatetype as
> aggregationvalue$aggregatetype,aggregationvalue.count as
> Dec 18 10:59:25 devel postgres[6889]: [4-3]
> aggregationvalue$count,aggregationvalue.stoptime as
> aggregationvalue$stoptime,aggregationvalue.starttime as
> Dec 18 10:59:25 devel postgres[6889]: [4-4] aggregationvalue$starttime from
> aggregationvalue where date_trunc($1, aggregationvalue.stoptime) between $2
> and $3 and
> Dec 18 10:59:25 devel postgres[6889]: [4-5] aggregationvalue.aggregatetype =
> $4 and split_part(aggregationvalue.value,':',1) = $5 and
> Dec 18 10:59:25 devel postgres[6889]: [4-6]
> split_part(aggregationvalue.value,':',2) like $6 and
> split_part(aggregationvalue.value,':',3) like $7 ]
>
> Dec 18 10:59:25 devel postgres[6889]: [6-1] LOG: duration: 2.000 ms
> statement: EXECUTE [PREPARE:select aggregationvalue.value as
> Dec 18 10:59:25 devel postgres[6889]: [6-2]
> aggregationvalue$value,aggre
Re: [GENERAL] 8.1.11 PREPARE problem?
thanks for the help.
between CAST(? as timestamp) and CAST(? as timestamp) fixes my problem.
because my second query uses a between the same day parameter it wasn't
being found when it was treated as a string, as a timestamp it works
fine. Apparently a simple query will convert the string to a timestamp
correctly, while they way I was doing things in java was forcing it to
be a string, thus killing my query.
I'm using a rather dense index:
CREATE INDEX stat_speed_big ON aggregationvalue (aggregatetype, value,
date_trunc('hour', stoptime), date_trunc('day', stoptime),
date_trunc('month', stoptime), date_trunc('year', stoptime),
split_part(aggregationvalue.value,':',1),
split_part(aggregationvalue.value,':',2),
split_part(aggregationvalue.value,':',3),
split_part(aggregationvalue.value,':',4),
split_part(aggregationvalue.value,':',5));
and messing with my dynamic SQL enough to make sure it hits the index
every time, that I got headed down the wrong track when it didn't work
consistently between applications, different driver versions, and did
work correctly on the old version, which doesn't server prepare
statements.
thanks again, if only for the moral support.
On Thu, 2008-12-18 at 14:52 -0500, Tom Lane wrote:
> Jeremiah Jahn writes:
> > This is with jdbc3-415. None of the (jdbc, or pg) change logs since then
> > have mentioned this problem. When run as a prepared statement the first
> > statement will execute and return results, while the next two seem to
> > execute, but return no results.
>
> It seems quite unlikely that there's a generic bug in prepared
> statements that no one has encountered till now. If you've got a real
> bug here I'm sure it's a lot narrower case than that. Can you put
> together a self-contained test case? Also, can you reproduce the
> problem on something newer than 8.1.11?
>
> 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] 8.1.11 PREPARE problem?
doh! my second prepared statement is getting prepared as all text, when
the second and third parameters should be timestamps.
argh! Is there some sort of logging, that says how prepared statements
are getting prepared?
On Thu, 2008-12-18 at 13:13 -0600, Jeremiah Jahn wrote:
> moving on:
> After digging through the JDBC source. It seems that the server is
> returning a 'C': // Command Status (end of Execute) before returning
> any data rows. So it doesn't look like JDBC is causing any problems.
>
> I've tried not running the first, and only running the second, and I get
> still no results. But as usual when running by hand, I do get results.
>
>
> the SQL that i'm running ends up looking like these when run by hand:
>
> 1) works as a prepared statement
> select aggregationvalue.value as aggregationvalue
> $value,aggregationvalue.aggregatetype as aggregationvalue
> $aggregatetype,aggregationvalue.count as aggregationvalue
> $count,aggregationvalue.stoptime as aggregationvalue
> $stoptime,aggregationvalue.starttime as aggregationvalue$starttime from
> aggregationvalue where date_trunc('month', aggregationvalue.stoptime)
> between '2007-11-01' and '2008-12-01' and
> aggregationvalue.aggregatetype = 'MONTHLY' and
> split_part(aggregationvalue.value,':',1) = 'JUDICI_PAGE' and
> split_part(aggregationvalue.value,':',2) like '%' and
> split_part(aggregationvalue.value,':',3) like '%' ;
>
> 2) does not work as prepared statement
> select aggregationvalue.value as aggregationvalue
> $value,aggregationvalue.aggregatetype as aggregationvalue
> $aggregatetype,aggregationvalue.count as aggregationvalue
> $count,aggregationvalue.stoptime as aggregationvalue
> $stoptime,aggregationvalue.starttime as aggregationvalue$starttime from
> aggregationvalue where date_trunc('day', aggregationvalue.stoptime)
> between '2008-12-18' and '2008-12-18' and
> aggregationvalue.aggregatetype = 'HOURLY' and
> split_part(aggregationvalue.value,':',1) = 'JUDICI_PAGE' and
> split_part(aggregationvalue.value,':',2) like '%' and
> split_part(aggregationvalue.value,':',3) like '%' ;
>
>
>
>
>
> On Thu, 2008-12-18 at 11:21 -0600, Jeremiah Jahn wrote:
> > This is with jdbc3-415. None of the (jdbc, or pg) change logs since then
> > have mentioned this problem. When run as a prepared statement the first
> > statement will execute and return results, while the next two seem to
> > execute, but return no results. When run by hand, not prepared , each
> > statement runs just fine. when using an older version of the jdbc driver
> > (7.3?), that does not call PREPARE, things work just fine. Looking at
> > the logs below, it sure looks like the statements are running, given the
> > different durations, which are correct for the differences between the
> > statements. help please.
> >
> >
> >
> >
> > Dec 18 10:59:15 devel postgres[6889]: [2-1] LOG: duration: 358.005 ms
> > statement: EXECUTE [PREPARE:select aggregationvalue.value as
> > Dec 18 10:59:15 devel postgres[6889]: [2-2]
> > aggregationvalue$value,aggregationvalue.aggregatetype as
> > aggregationvalue$aggregatetype,aggregationvalue.count as
> > Dec 18 10:59:15 devel postgres[6889]: [2-3]
> > aggregationvalue$count,aggregationvalue.stoptime as
> > aggregationvalue$stoptime,aggregationvalue.starttime as
> > Dec 18 10:59:15 devel postgres[6889]: [2-4] aggregationvalue$starttime
> > from aggregationvalue where date_trunc($1, aggregationvalue.stoptime)
> > between $2 and $3 and
> > Dec 18 10:59:15 devel postgres[6889]: [2-5] aggregationvalue.aggregatetype
> > = $4 and split_part(aggregationvalue.value,':',1) = $5 and
> > Dec 18 10:59:15 devel postgres[6889]: [2-6]
> > split_part(aggregationvalue.value,':',2) like $6 and
> > split_part(aggregationvalue.value,':',3) like $7 ]
> >
> > Dec 18 10:59:25 devel postgres[6889]: [4-1] LOG: duration: 25.001 ms
> > statement: EXECUTE [PREPARE:select aggregationvalue.value as
> > Dec 18 10:59:25 devel postgres[6889]: [4-2]
> > aggregationvalue$value,aggregationvalue.aggregatetype as
> > aggregationvalue$aggregatetype,aggregationvalue.count as
> > Dec 18 10:59:25 devel postgres[6889]: [4-3]
> > aggregationvalue$count,aggregationvalue.stoptime as
> > aggregationvalue$stoptime,aggregationvalue.starttime as
> > Dec 18 10:59:25 devel postgres[6889]: [4-4] aggregationvalue$starttime
[GENERAL] Thanx for 8.3
Just wanted to say thank you for version 8.3. The ordered indexing has dropped some of my search times from over 30 seconds to 3. I've been beating my head against this issue for over 8 years. I will drink to you tonight. thanx again, -jj- -- When you're dining out and you suspect something's wrong, you're probably right. -- 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] Thanx for 8.3
On Fri, 2009-01-09 at 08:17 +0100, Reg Me Please wrote: > On Friday 09 January 2009 00:10:53 Jeremiah Jahn wrote: > > Just wanted to say thank you for version 8.3. > > > > The ordered indexing has dropped some of my search times from over 30 > > seconds to 3. I've been beating my head against this issue for over 8 > > years. I will drink to you tonight. > > > > thanx again, > > -jj- > > > > > > > > -- > > When you're dining out and you suspect something's wrong, you're probably > > right. > > Give also CLUSTER a try. > And partial indexes also. I've had clusters going since they became available. They still required massive sequential scans and with a dedicated disk array w/ a sustained I/O rate of 600MB/s it still took 30 seconds. My data has about 25 new/updated entries per day, so the clusters just couldn't keep up. 70% of my problem was sorting, followed by a complex join. Now that the sorting is O(n), I've modified things to use a search table that is basically a select into of the join I always had to do. Had I done this before, I wouldn't have had the improvements to justify the added complexity to my system. I use partial indexes in other places, but these are name searches where someone wants all the 'SMITHS%' in half the state of Illinois who've been 'convicted' of 'aggravated battery' 'in the last 5 years' and have traffic tickets'; It's difficult to come up with partials when the queries are not predictable. Nor have I ever had the budget to get enough memory to keep these tables in memory. There just always been a limit to the amount of hardware(money) I can throw as something. Of course that's what makes it fun and challenging. Now if there was just simple way to make some sort of persistent view that could have indexes on it, so that complex joins could be sped up, in stead of making non-normal tables. (hint hint :) > > Prosit! > > -- > Fahrbahn ist ein graues Band > weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] altre table and large object deletion
will an alter table that removes an oid type also remove all of the associated large objects. I've been using blobs but have converted to byte arrays, now I need to get rid of all of the blobs. Will this be enough? followed by a vaccum of course. -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] pg_largeobject and oid mistmach after restore
when I run the following two commands all of my OIDs for my blobs (about 5.5 million of them) no longer reference anything in pg_largeobject. All of the loid values change. the relevant output from pg_restore: pg_restore: restoring data for table "BLOBS" pg_restore: connecting to database "copa" as user "copa" pg_restore: creating table for large object cross-references pg_restore: restored 5575606 large objects pg_dump -F c -v -b -o -U copa copa > judici.pgsql pg_restore -C -d template1 -F c -v -U copa < europa/judici.pgsql thanx, -jj- -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Where is initdb?
although it will be taken care of, make sure that initdb sets the local language to C or your string indexes will not be used.. Unless this has been fixed and en_US works as as well? On Mon, 2004-01-26 at 13:32, Peter Eisentraut wrote: > Jerome Lyles wrote: > > I have installed Postgresql 7.4 on a Suse 9.0 system using apt. > > I cannot do this: > > > > /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data > > Just run > > /etc/init.d/postgresql start > > and it will be taken care of. Read the README files in > /usr/share/doc/packages/postgresql-* to learn about how the packages > are layed out. > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_largeobject and oid mistmach after restore
my column types are and have always been oid, but for some weird reason when I dump and restore, I can't seem to reference my blobs anymore. I have done this a zillion times, and just can't figure our what the heck I'm doing wrong (this time). On Mon, 2004-01-26 at 14:03, Tom Lane wrote: > Jeremiah Jahn <[EMAIL PROTECTED]> writes: > > when I run the following two commands all of my OIDs for my blobs (about > > 5.5 million of them) no longer reference anything in pg_largeobject. > > All of the loid values change. > > pg_dump/pg_restore do not (and cannot) arrange for large objects to have > the same OIDs after restore as they had before. What they do try to do > is update references to the large objects to have the new OID values. > Last I checked, they look in columns of types "oid" and "lo" (if "lo" > exists) for such references. Maybe you were engaging in some type > punning, like storing your references in int4 or int8 columns? > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Where is initdb?
I think it's fair to say that out of the box and RPM install should not result in indexes not being used because the LANG is set to something other than 'C'. I'm all for reading the manual and tuning later, but that is something that can't be changed without a complete dump/initdb/restore. It's just always seemed kind of misleading to me.. -jj- On Mon, 2004-01-26 at 13:51, Martín Marqués wrote: > Mensaje citado por Jeremiah Jahn <[EMAIL PROTECTED]>: > > > although it will be taken care of, make sure that initdb sets the local > > language to C or your string indexes will not be used.. Unless this has > > been fixed and en_US works as as well? > > I think that if you really need very fast indexes, the first thing to do is read > the instalation manual and configure very carefully the postgres acount > (envioronment variables, etc). -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Filesystem vs. Postgres for images
There has got to be some sort of standard way to do this. We have the same problem where I work. Terabytes of images, but the question is still sort of around "BLOBs or Files?" Our final decision was to use the file system. We found that you didn't really gain anything by storing the images in the DB, other than having one place to get the data from. The file system approach is much easier to backup, because each image can be archived separately as well as browsed by 3rd party tools. -jj- On Tue, 2004-04-13 at 07:40, Cott Lang wrote: > On Tue, 2004-04-13 at 01:44, Michal Hlavac wrote: > > Hello, > > > > I am working on web portal. There are some ads. We have about 200 000 > > ads. Every ad have own directory called ID, where is 5 subdirectories > > with various sizes of 5 images. > > > > Filesystem is too slow. But I don't know, if I store these images into > > postgres, performace will grow. > > > Consider breaking your directories up, i.e.: > > /ads/(ID % 1000)/ID > > I use that for a system with several million images, works great. I > really don't think putting them in the database will do anything > positive for you. :) > > > > > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Filesystem vs. Postgres for images
I tried the bytea types, but the parsing done by the system on insert etc. was so bad that it made it usable for me. Our solution is to keep all of the metadata in the db plus an id and then a web service that gets the image from the FS. On Tue, 2004-04-13 at 09:05, Joshua D. Drake wrote: > Hello, > > No standard way that I know of :). We tend to use BLOBS because we can > have associated tables > with metadata about the images that can be searched etc Of course > you could that with the filesystem > as well but we find blobs easier. > > I will say we tend to use BLOBS or Bytea. > > J > > > Jeremiah Jahn wrote: > > >There has got to be some sort of standard way to do this. We have the > >same problem where I work. Terabytes of images, but the question is > >still sort of around "BLOBs or Files?" Our final decision was to use the > >file system. We found that you didn't really gain anything by storing > >the images in the DB, other than having one place to get the data from. > >The file system approach is much easier to backup, because each image > >can be archived separately as well as browsed by 3rd party tools. > > > >-jj- > > > > > >On Tue, 2004-04-13 at 07:40, Cott Lang wrote: > > > > > >>On Tue, 2004-04-13 at 01:44, Michal Hlavac wrote: > >> > >> > >>>Hello, > >>> > >>>I am working on web portal. There are some ads. We have about 200 000 > >>>ads. Every ad have own directory called ID, where is 5 subdirectories > >>>with various sizes of 5 images. > >>> > >>>Filesystem is too slow. But I don't know, if I store these images into > >>>postgres, performace will grow. > >>> > >>> > >>Consider breaking your directories up, i.e.: > >> > >>/ads/(ID % 1000)/ID > >> > >>I use that for a system with several million images, works great. I > >>really don't think putting them in the database will do anything > >>positive for you. :) > >> > >> > >> > >> > >> > >> > >>---(end of broadcast)--- > >>TIP 3: if posting/reading through Usenet, please send an appropriate > >> subscribe-nomail command to [EMAIL PROTECTED] so that your > >> message can get through to the mailing list cleanly > >> > >> -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Filesystem vs. Postgres for images
Your code is retrieving the file from the file system. It doesn't have to be accessible from the web server at all. Our current design uses a JDBC connection to the database for the metadata (digital signature,path,name,file type, etc..) and a SOAP call to the same server (but doesn't have to be) to retrieve/store the image data. -jj- On Wed, 2004-04-14 at 08:15, Alex wrote: > Hi, > is the file system approach really easier and faster? What if you need > to protect the image data e.g. you dont want users just to just dowload > the pictures directly from your website? > > -a > > Jeremiah Jahn wrote: > > >There has got to be some sort of standard way to do this. We have the > >same problem where I work. Terabytes of images, but the question is > >still sort of around "BLOBs or Files?" Our final decision was to use the > >file system. We found that you didn't really gain anything by storing > >the images in the DB, other than having one place to get the data from. > >The file system approach is much easier to backup, because each image > >can be archived separately as well as browsed by 3rd party tools. > > > >-jj- > > > > > >On Tue, 2004-04-13 at 07:40, Cott Lang wrote: > > > > > >>On Tue, 2004-04-13 at 01:44, Michal Hlavac wrote: > >> > >> > >>>Hello, > >>> > >>>I am working on web portal. There are some ads. We have about 200 000 > >>>ads. Every ad have own directory called ID, where is 5 subdirectories > >>>with various sizes of 5 images. > >>> > >>>Filesystem is too slow. But I don't know, if I store these images into > >>>postgres, performace will grow. > >>> > >>> > >>Consider breaking your directories up, i.e.: > >> > >>/ads/(ID % 1000)/ID > >> > >>I use that for a system with several million images, works great. I > >>really don't think putting them in the database will do anything > >>positive for you. :) > >> > >> > >> > >> > >> > >> > >>---(end of broadcast)--- > >>TIP 3: if posting/reading through Usenet, please send an appropriate > >> subscribe-nomail command to [EMAIL PROTECTED] so that your > >> message can get through to the mailing list cleanly > >> > >> -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] db schema diff
Although now consistent, is there a way to strip out the comments? since
they differ between dbs? Right now, I use grep, but I have this need to
live in a perfect world were everything is just a command line
option..:)
On Mon, 2004-04-12 at 16:57, Tom Lane wrote:
> Igor Shevchenko <[EMAIL PROTECTED]> writes:
> > On Monday 12 April 2004 22:38, you wrote:
> >> ${PG_DUMP} -s -p 5432 mydb|${GREP} -v '^--'|${CAT} -s >5432.sql
> >> ${PG_DUMP} -s -p 5433 mydb|${GREP} -v '^--'|${CAT} -s >5433.sql
> >> ${PG_DUMP} -s -p 5434 mydb|${GREP} -v '^--'|${CAT} -s >5434.sql
> >>
> >> ${DIFF} 5432.sql 5433.sql > 5432-5433.diff
> >> ${DIFF} 5433.sql 5434.sql > 5433-5434.diff
>
> > I used to do exactly this but the pg_dump order is different on my two
> > servers, and this generates lots of false diffs.
>
> FWIW, CVS tip pg_dump has been modified to produce a consistent (and
> safe) dump order, so its output should be a lot more useful for schema
> diff'ing purposes than previous releases were.
>
> It should work to build a current snapshot and use its pg_dump against
> older servers, if you need a solution now.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
Jeremiah Jahn <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] db schema diff
by comments, I mean these: -- -- TOC entry 16 (OID 166152808) -- Name: user_credit_card; Type: TABLE; Schema: public; Owner: copa -- these have really gone away in a new version, and if so, which one..? On Wed, 2004-04-14 at 10:26, Tom Lane wrote: > Jeremiah Jahn <[EMAIL PROTECTED]> writes: > > Although now consistent, is there a way to strip out the comments? since > > they differ between dbs? > > Not any more they don't ... unless you use the --verbose option, > pg_dump's comments should look the same too. > > regards, tom lane -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] cross table indexes?
I was wondering if there is something I can do that would act similar to a index over more than one table. I have about 3 million people in my DB at the moment, they all have roles, and many of them have more than one name. for example, a Judge will only have one name, but a Litigant could have multiple aliases. Things go far to slow when I do a query on a judge named smith. Does any one know a possible way to speed this up? I would think that In a perfect world there would be a way to create an index on commonly used joins, or something of that nature. I've tried partial indexes, but the optimizer feels that it would be quicker to do an index scan for smith% then join using the pkey of the person to get their role. For litigants, this makes since, for non-litigants, this doesn't. thanx for any insight, -jj- -- "You can't make a program without broken egos." -- Jeremiah Jahn <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] cross table indexes?
I was wondering if there is something I can do that would act similar to a index over more than one table. I have about 3 million people in my DB at the moment, they all have roles, and many of them have more than one name. for example, a Judge will only have one name, but a Litigant could have multiple aliases. Things go far to slow when I do a query on a judge named smith. Does any one know a possible way to speed this up? I would think that In a perfect world there would be a way to create an index on commonly used joins, or something of that nature. I've tried partial indexes, but the optimizer feels that it would be quicker to do an index scan for smith% then join using the pkey of the person to get their role. For litigants, this makes since, for non-litigants, this doesn't. thanx for any insight, -jj- -- "You can't make a program without broken egos." signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Large objects [BLOB] again - general howto
Here's a quick list of my experiences with BLOB's and such. Performance is just fine, I get about 1M hits a month and haven't had any problems. Use a BLOB if you don't need to search though the data. The main reason being that bytea and text types are parsed. To explain, your entire SQL statement has to be parsed to make sure it's valid, this includes you 100 meg jpeg. Slow, REAL slow. I have found that it is best to have a separate connection for BLOB's and one for everything else. Mind you, this is with Java, but the autocommit settings on the connection don't appear to be thread safe, so in high traffic you can accidentally cut off a transfer, or stop one before it ever starts. -jj- On Sun, 2003-11-23 at 10:48, Randolf Richardson, DevNet SysOp 29 wrote: > > Someone asks about performance of Large objects [LO] in PgSql [PG]. It > > was interesting for me, because I didn't work yet with them yet and I > > will have to soon. I tried search the web, doc and mailinglists, but I > > didn't found any adequate reply. I would be happy, of someone, who have > > experimence with them (or who know PG internals :) can make it clean. I > > think I am not alone, who is interested in this topic. > > You're certainly not alone. I haven't done this yet, but will need to > in the near future with a web-based database project I'm in the midst of > planning at the moment which will involve allowing users to upload PNG, GIF > and JPeG images and having them displayed in a sort of "picture album" type > layout. > > > In past time, where row data was limited by single tupple (8K-32K), LO > > was really needed, if you wanted to store more than 8K per row. Older > > implementation issues are described in documentation. > > I'm somewhat concerned about this, but not because of the large object > support (which I understand is handled by not storing the object data > directly in the row, thus circumventing this limitation altogether), rather > I think about scalability with large rows that have a lot of columns that > sum up to more than 32,768 bytes in size. > > > Today, there is 1G limit per row. Is there reason to use LO instead of > > storing simple 'text' into database table ? (let's ignore 2G limit on > > That depends on the needs of your application. If all you're storing > is text data, then the choice is yours. If you're storing binary data, > then simple text probably won't be appropriate. > > > LO, which can make LO interesting in some cases :)Documentation says it > > is obsolvete. But documentation also doesn't tell anything about > > performance, suggestion about using, etc. > > > > By 'access' to binary file, I understand: > > > > - reading (and then working with its contents on server side) > > - sending (read and send as-is to client, without workaround) > > - updating (from contents which I get from somewhere) > > > > LO means storing file using large objects, > > column means storing file as 'text' column (standard way). > > I don't know about this. Hopefully someone else who has experience in > this area will jump in here and clear this matter up. > > > Let's say I am having 'file' - smaller (tenhts of Kb) - let's say some > > JPG image (photo of beer :) The file is 'readed' as is and passed to > > client (browser, app, whatever). In this case, would be probably better > > to store file as normal column, because it is easier to access it. This > > is one of typical use. Every time I will write to that image, SQL parser > > need to parse all datas, allocate proper memory, I think, real > > differnece will not be too big, since picture having just tenths of Kb. > > If you can ensure that the image size will never exceed the width of > your column, then you'll be okay, but note that image sizes can vary widely > depending on a number of factors in addition to image dimensions, one of > which that is often overlooked is the possibility of poorly designed > compression algorithms in the applications used to generate the JPeG image > or the user selecting minimal compression because they want a better > quality image (I do this sometimes in the web development I do, but only > when it's appropriate). > > Remember, however, that storing binary data in a column intended for > text might cause some problems down the road (even if it doesn't now) since > it will contain unexpected data. You probably should look into the text > encoding systems out there to see if there could be some potential problems > with, for example, your data being misinterpreted as Chinese text. > > > But let's have the same picture, but in pretty fine resolution for > > printing. File size will be megabytes and more [let's say 10Mb]. > > Ofcourse, file should be stored in DB because of data integrity. In this > > case, we will need pretty much memory while reading/updating. All the > > time. And if I will get many simil
