Re: [GENERAL] PGSQL 9.6.2 unable to find readline
On Tuesday, March 7, 2017 3:35 PM, John Iliffe wrote: > > Trying to compile pgsql 9.6.2 on Fedora 25 > > I get the following message: > > configure:9345: error: readline library not found > If you have readline already installed, see config.log for details on the > Use --without-readline to disable readline support. > pgac_cv_check_readline=no > > libreadline does exist: > > [John@prod04 postgresql-9.6.2]$ rpm -qv readline > readline-6.3-8.fc24.x86_64 You probably need readline-devel-6.3-8.fc24.x86_64 > > Thanks. > > John -- 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] SELECT * and column ordering
On Wed, Jan 16, 2013 at 11:45 AM, Rob Sargent wrote: > What environment are you in. In jdbc you can address the resultset by > column name. C++, so it's quite inefficient to always use names. Chris Angelico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELECT * and column ordering
Hi! (I'm Chris Angelico posting from a work account - usually I'm here under the name ros...@gmail.com.) I've run into a bit of an awkwardness in PostgreSQL setup and am hoping for some expert advice. Several of the tables I work with have two groups of fields: standard fields and "free fields". The standard fields have fixed names and will always exist; the free fields could be anything. In many cases, I want to enumerate all fields, including free ones, and for that I use "SELECT * FROM tablename". Normally, the standard fields are at the beginning of the table, having been created first. This is very convenient, as it lets me iterate over them first, and then pick up the free fields after. (Or alternatively, pick up a specific standard field by its index.) New free fields can be created at any time, and the program will happily pick them up and work with them. Order among free fields never matters. The problem comes when I want to add a new standard field. PostgreSQL currently doesn't have any way for me to insert a field into the beginning of a table, so I can't put it where it would have been if it had existed already. The table could be quite large, with several hundred free fields, and could have any number of rows. There are a number of options open to me. As per http://wiki.postgresql.org/wiki/Alter_column_position I could create a duplicate table, or duplicate columns. Both would involve a lot of disk churning, but that's my fallback if nothing else works. Ideally, what I'd like to do is become independent of the physical column order. If I were looking for just the standard fields, I could explicitly enumerate them in the SELECT statement, which would solve the problem. But doing this with an unknown set of fields requires the code either: * List fields via a catalogue table, join them into a SELECT list, and process that; or * Enumerate the system fields and then use a star, eg "SELECT _foo,_bar,_quux,* FROM tablename" The former requires two round-trips to the database instead of one. Since the application and database are on different computers, this could seriously impact performance, especially as this is a very common operation. The latter will give duplicates of the system fields, as the * expands to include them. Is there any way to do something like "SELECT x,y,z,ALL-THE-REST" that doesn't include the columns already named? If not, which of the options above, or what alternative, would you recommend? All advice gratefully appreciated! Chris Angelico -- 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] Drupal and PostgreSQL - performance issues?
I am also evaluating Drupal + PostgreSQL at the moment. We are building a local government website/intranet that doesn't need to be lightning fast or handle millions of hits a day, but it does need to be rock solid and potentially needs to manage complex business processes. So PostgreSQL seems a good choice. However, PostgreSQL support in the PHP CMS world seems lacking. Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable, but who really knows where it will end up? Can anyone recommend an alternative CMS with the features and flexibility of Drupal that supports PostgreSQL 100%? What about the Python world, what is Plone like with PostgreSQL support? I don't really want to kick off another round of Python vs PHP, just looking for a CMS that is a good match for PostgreSQL. Mick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Starting PostgreSQL
Sorry folks, a perennial one I'm sure ... I have read the manual and Googled for a couple of hours but still can't connect to PostgreSQL 8.3.4 (the PGDG RPMs running on an up to date CentOS 5.2). I continually get this message: psql: could not connect to server: No such file or firectory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PDSQL.0"? Yes, the server is running as 'ps -aux' and 'netstat -l' and 'service postgresql status' all confirm. service postgresql start/stop/restart works without errors pg_ctl start/stop/restart works without errors There is no socket file in /tmp. I believe I have PG configured to listen on port 5432 anyway: listen_addresses = '*' port = 5432 Is that enough to make PG listen on a port ... the docs seem to be saying that? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.4 RPMs
Sorry if this is the wrong list, I'm not totally up with the PG ecosystem. I'm looking for RHEL/CentOS RPMs for version 8.4 for testing purposes (with Drupal). I can find an RPM to install a yum repo at yum.pgsqlrpms.org, but not the RPMs themselves. The yum repo is not much use to me as the machine I want to install on is not connected to the net. www.postgresql.org/ftp has 8.3 RPMs, but no 8.4. I know the 8.4 RPMs must exist somewhere if they're in the pgsqlrpms repo, but where? Thanks Mick -- 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] php + postgresql
Well no PHP is conceptual undisciplined and confusing. I would not compare this with Postgresql itself which is very professional developed with a great vision. PHP is just and always was a hack. I didn't mean to compare PG and PHP at the level of engineering quality, but to suggest that perhaps both suffer from people continuing to hold rigid preconceptions about them based on how things were 5 or 10 years ago. Anyway, while I'm quite happy to continue banging out things that "just work" in PHP for the time being, you suggest (in a subsequent post) that there is one scripting language in particular that you'd use ... might I enquire which language that is, and why? Just curious, I'm definitely not looking for an ideological debate. Re the possible heightened level of animosity to PHP in PG circles, if it exists, could it have anything to do with PHP's close association with MySql? The animosity, by the way, seems to go both ways, I think I saw something about Rasmus Lerdorf bagging PostgreSQL on Slashdot(?) recently. Personally, I'm not overly concerned either way. I'm happy to leave the academic debates to those with the time to pursue them. I'm the first to admit I know little about the art and science of relational database design and admin. But up to this point, I haven't needed to. It doesn't take rocket science to store and retrieve some text for a few web pages in a database. Anyway, this is proving an interesting, lively and helpful community, hope to learn lots more about doing things the PostgreSQL way ... with PHP :-). Mick -- 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 base tables design questions for: user saved forms, user parameters
I'm facing a very similar problem where I work (local govt) where we want to put around 100 forms online. The idea of 100 seperate database tables is too painful to contemplate. Depending on the nature of the data, I suppose, both the structured text string and the serialised object options sound viable. I'm very new here, but I think I've seen mention somewhere in the docs about PG being able to store arrays? If this is true, it might be another option. Mick -- 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] php + postgresql
Is there any special reason to use PHP? There are a couple other scripting languages useable for the web which do all have better abstration available. (afaic even PHP does have some more abstration to just using pg* functions) Well, yes, there are alternatives of course and I could write this stuff in perl or python but it'd take me 10 times as long because my experience is elsewhere. Learning new stuff is always good, but at the end of the day I get paid for making stuff work on time and in budget ... mostly :-) I think that PHP (like PostgreSQL, perhaps?) suffers from a reputation hangover from years ago. PostgreSQL was supposedly "slow", PHP is supposedly "undisciplined" and "unprofessional". You sure can still write spaghetti with PHP5 if you want to, but you can also write decent code with planning and standards. But good, bad or ugly, it's what I personally am most productive in. I have used PHP's PEAR DB abstraction class many times. It doen't really save much time or effort writing code, and has a performance overhead. I don't need to allow the possibility of switching to another database and stuff like that. Mick -- 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] php + postgresql
You need something like this:
$query = "select id, name from tablename";
$result = pg_query($query);
while ($row = pg_fetch_array($result)) {
$content = $row[0];
}
That's actually what I was using.
The scoping wasn't the issue either.
Today I switched back to pg_connect() from pg_pconnect(), made some
changes to my overall architecture and re-wrote my database stuff. Then
re-booted.
Not sure what fixed it but all working now. I'm only working on a draft
"skeleton" right now so am free to fiddle.
Keep finding cool features in PostgreSQL, I think I'm sold!
Thanks
Mick
--
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] php + postgresql
Thanks again for replies. I know those questions were pretty vague. I need to set up some methodical test scripts that replicate my problems, so that it is clear what is going on. There does seem to be some evidence of problems historically with PHP and persistent connections in PostgreSQL, on the PHP forums. The advice is typically to avoid them. Mick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] php + postgresql
First, thanks to everyone who responded to my newbie questions
yesterday, all clear now.
I spent most of today struggling with apparently inconsistent behaviour
while running SELECT statements on PG 8.1.9 using PHP 5.1.6 (these are
both as supplied with CentOS 5.1, a fairly conservative distro).
It seems that some of PHP's PG functions have changed recently, are
there any known issues with them?
1.
I ended up using pg_prepare() and pg_execute() as pg_query() alone just
didn't seem to work. But SELECT statements seemed to be cached or
persistent in some way, such that they "lived" beyond the life of the
PHP script. Is there something I need to know about persistent behaviour
in PG that doesn't exist in MySQL?
2.
Another problem was that no matter how many times I checked and
re-checked code, or which pg_fetch_* function I used, copying an array
member and trying to use it later just would not work, eg
while ($row = pg_fetch_array($query)) {
$content = $row[0]
}
echo $content;
$content was always 'undeclared'.
3.
Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ... is
this required behaviour (PHP docs don't appear to discuss this)?
4.
Another weird one was that this statement always failed:
$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'
while this one always worked:
SELECT fld_content FROM tbl_page WHERE fld_pid=1
in a three column table:
fld_pid serial PRIMARY KEY,
fld_name varchar(100) NOT NULL,
fld_content text NOT NULL
while everything worked fine from the psql console.
... but this post is getting too unwieldy. I am reading documentation
but am also under some pressure to get basic things up and running. Any
pointers to good documentation covering PHP + PG, or any well known gotchas?
PS If people want to throw MySQL->PostgreSQL gotchas at me I'm happy to
collate and write up.
Thanks again
Mick
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A couple of newbie questions ...
I've worked as a web developer on mostly small business websites for the
past seven years, and while I've had some limited experience with older
versions of PostgreSQL (7.* ??), I've mostly used MySQL all this time.
I now work for local govt and am building a large intranet-like system
which ultimately could provide storage for and various interfaces to a
significant proportion of my organisation's data including financial data.
I'm convinced that PostgreSQL's performance is not an issue (both
because it's improved and traffic will be relatively low anyway), and
that the benefits of PostgreSQL's advanced features are too good to
ignore. I'm hoping to shift quite a bit of data processing into the
database.
So anyway, life story aside, I have a couple of very newbie questions
after tinkering with PostgreSQL 8.1.9 for a day converting some
PHP/MySQL code:
1. Is a SEQUENCE what I use instead of auto_increment?
2. Does this work in PostgreSQL:
INSERT INTO table VALUES ('x','y','z')
or do I need to do this
INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')
?
3. Does this work in PostgreSQL:
INSERT INTO table VALUES ('','y','z')
where the empty first item is intended for an auto_increment/SEQUENCE id
field?
If not, what is an alternative?
Thanks
Mick
--
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] Int8 problem
Tom your right. I take a detailed look at it and it fails exactly at 2^31 . 2^31-1 = 2147483647 --> 2147483647 2^31= 2147483648 --> -2147483648 2^31+1= 2147483649 --> -2147483647 . 10^10--> 1410065408 10^12--> -727379968 ... But how could i compile the int8 corectly? - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "admin" <[EMAIL PROTECTED]> Cc: "Jerome Raupach" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: 27 septembrie 2000 08:43 Subject: Re: [GENERAL] Int8 problem > "admin" <[EMAIL PROTECTED]> writes: > > But on the alpha i got no error message no worning and inserts a wrong > > number if the number is bigger than 10^13 . > > 10^13? But values > 2^31 work? Man, that's really weird ... I was > expecting a failure at 2^31 if int8 wasn't compiled correctly. > I can't think what might fail at 10^13. Ideas anyone? > > It might help to see an exact table of equivalences, ie, so much > in, so much out. > > regards, tom lane
Re: [GENERAL] Int8 problem
Hi, I have intalled Postgres 7.0.2 recently on 3 normal PC with RedHat and Slackware to try its behavior but its working good. int8 thakes number much bigger then 10^13. And if the number is to big (about 10^19 ) it generate an error message (ERROR: int8 value out of range: ...) and don't insert it into the table. But on the alpha i got no error message no worning and inserts a wrong number if the number is bigger than 10^13 . regard Hanos Felix - Original Message - From: "Jerome Raupach" <[EMAIL PROTECTED]> To: "admin" <[EMAIL PROTECTED]> Sent: 26 septembrie 2000 14:48 Subject: Re: [GENERAL] Int8 problem > your number is truncated to the biggest int8 ( 2*10^10, I think). > try with 'numeric'. > > Jerome. > > > admin wrote: > > > > Hi > > I have intalled Postgres 7.0.2 on a Compaq Alpha with DigitalUnix. > > If I insert a bigger number like 10^13 in an int8 type field i'm getting no > > error messages, no warnings, but if I check the value inserted i receive > > totaly wrong number like 2543 > > If you have any suggestions please let me know. > > > > Thank any way.
Re: [GENERAL] Int8 problem
- Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "admin" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: 26 septembrie 2000 16:31 Subject: Re: [GENERAL] Int8 problem I send you the outputs maybe you will find an answer. Thanks. Hanos Felix > "admin" <[EMAIL PROTECTED]> writes: > > I have intalled Postgres 7.0.2 on a Compaq Alpha with DigitalUnix. > > If I insert a bigger number like 10^13 in an int8 type field i'm getting no > > error messages, no warnings, but if I check the value inserted i receive > > totaly wrong number like 2543 > > Hm. It sounds like configure failed to find any compiler support for > 8-byte ints, so it gave up and compiled int8 as int4. But an Alpha > ought to have 8-byte ints. May we see the output files from configure > (config.status, config.log)? > > regards, tom lane config.log config.status
[GENERAL] Int8 problem
Hi I have intalled Postgres 7.0.2 on a Compaq Alpha with DigitalUnix. If I insert a bigger number like 10^13 in an int8 type field i'm getting no error messages, no warnings, but if I check the value inserted i receive totaly wrong number like 2543 If you have any suggestions please let me know. Thank any way.
Re: [GENERAL] DB replication
I've had the same question, I believe the best it gets will be something like running rsync over an encrypted (if needed for security) tunnel (ssh). But you'll have to user some dump utility as far as I know. Cheers Wim. - Oorspronkelijk bericht - Van: Differentiated Software Solutions Pvt. Ltd. +ADw-diffs+AEA-vsnl.com+AD4- Aan: +ADw-pgsql-general+AEA-postgresql.org+AD4- Verzonden: Friday, March 24, 2000 9:19 AM Onderwerp: +AFs-GENERAL+AF0- DB replication +AD4- Hi, +AD4- +AD4- We have an application which has databases in 2 different machines. +AD4- The databases are small (25 MB). +AD4- +AD4- Every 15 minutes we want DB of one machine to be synced with another +AD4- machine. Are there ready made utilities in postgres to do this. +AD4- One way is to take a pgdump, tar it, ftp it and then restore it into the +AD4- other machine. +AD4- This has a problem if I want to sync selected tables. Also this means the +AD4- entire data gets reloaded, whereas only the incremental changes should get +AD4- dumped. +AD4- +AD4- Any help is appreciated. +AD4- +AD4- Murali +AD4- +AD4- Differentiated Software Solutions Pvt. Ltd., +AD4- 176, Gr. Floor, 6th Main +AD4- 2nd Block RT Nagar +AD4- Bangalore - 560 032 +AD4- India +AD4- Ph: 91 80 3431470 +AD4- email : diffs+AEA-vsnl.com +AD4- http://www.diffs-india.com +AD4-
No Subject
configset pgsql-general delivery_rules << ENDAAK /(\.fi|\.no|\.dk|\.se)$/ sort maxaddrs=50 minseperate=5 hosts=(towerguard.unix.edu.sollentuna.se=(esmtp,pipelining,onex)) backup=(hub.org=(esmtp,pipelining,onex)) /(\.nz)$/ sort maxaddrs=50 minseperate=5 hosts=(postgresql.godzone.net.nz=(esmtp,pipelining,onex)) backup=(hub.org=(esmtp,pipelining,onex)) /(\.ch)$/ sort maxaddrs=50 minseperate=5 hosts=(mail.omedia.ch=(esmtp,pipelining,onex)) backup=(hub.org=(esmtp,pipelining,onex)) /(\.de)$/ sort maxaddrs=50 minseperate=5 hosts=(smtp.rhein-zeitung.de=(esmtp,pipelining,onex)) backup=(hub.org=(esmtp,pipelining,onex)) ALL sort maxaddrs=50 minseperate=5 hosts=(news.hub.org=(esmtp,pipelining,onex)) backup=(hub.org=(esmtp,pipelining,onex)) ENDAAK configset pgsql-general digests << ENDAAN digest|| 20K| 40K | 1d| || ENDAAN configset pgsql-general master_password = 3asys3t0p configset pgsql-general subject_prefix = [GENERAL]
[GENERAL] Date problem
Hi, I'm trying to do something with dates which is proving to be a bit
tricky.
I'm trying to get the current "date" and add 3 days to this.
I've tried:
$date = "(now::date) + ('3 days'::interval)";
DBD::Pg::st execute failed: ERROR: parser: parse error at or near "3"
... and :
$date = "(now::date) + (\\'3 days\\'::timespan)";
DBD::Pg::st execute failed: ERROR: Bad date external representation
'(now::date) + ('3 days'::timespan)'
Needless to say I'm using this date creation string as a parameter of a
DBD::Pg CGI query, hence further complications with apostrophes.
I've found nothing concrete in the documentation about how to do this, and
the closest thing to an answer came from this newsgroup. No luck so far
tho'
does anyone know how to do this and possibly and source of good
documentation on this type of thing.
Regards
Scott McDaid
edNET
t: +44 131 625 5557 (direct dial)
t: +44 131 466 7003 (office)
[GENERAL] rule or trigger on select?
How can I update on select? >From the User's Guide, it seems triggers can only be used on INSERT, UPDATE and DELTE events. As for rules, the guide says they can be used on SELECT. When I actually tried updating on select using rules, here's what I got: test=> CREATE RULE tab_rule AS ON select test-> TO tab test-> DO UPDATE tab SET stats=stats+1; ERROR: only instead-select rules currently supported on select My ultimate goal is to keep statistics for tab counting how many times records are being selected. Unfortunately, I cannot simply use UPDATE since my SELECT queries use LIMIT, which is not supported by UPDATE. Looking forward to any suggestions, Marc
[GENERAL] indices on tab1.a=tab2.a
I have unfortunately deleted a message to pgsql-general today which contained a query like: SELECT tab1.b, tab2.c FROM tab1, tab2 WHERE tab1.a=tab2.a; There was also a UNION following, but my memory fails me. My question is though, can an index be used for the above query? When I try it with an index on tab1.a and tab2.a, I get two hash joins and two seq scans. If there's any way to optimise this query, please let me know, Marc
RE: [GENERAL] Memory leak in FreeBSD?
What is maxusers set to in your kernel? One prolem I had was that postgresql was using more filedescriptors that my kernel could handle. If you'd like to check your current filedescriptor status and your max, try: pstat -T. If that is your problem, change your maxusers to a suitable number and recompile your kernel. > FreeBSD port: I don't know enough to know what difference that might make. > Any suggestion you have would be appreciated: thanks. > > > Did you upgrade from source or from the freebsd ports? > > > > > We upgraded to version 6.5.2 recently, running on FreeBSD 3.0. Now we > > are > > > having problems with moderately complex queries failing to complete > > (backend > > > terminating unexpectedly; last one crashed the server). The most likely > > > explanation appears to be a memory leak. Is there any known problem > > with > > > FreeBSD? >
Re: [GENERAL] Memory leak in FreeBSD?
Did you upgrade from source or from the freebsd ports? > We upgraded to version 6.5.2 recently, running on FreeBSD 3.0. Now we are > having problems with moderately complex queries failing to complete (backend > terminating unexpectedly; last one crashed the server). The most likely > explanation appears to be a memory leak. Is there any known problem with > FreeBSD?
Re: [GENERAL] Re: can't seem to use index
> > I'm trying to use an index on a varchar(32) field, but explain keeps > > retuning a sequential scan. This is my table and index: > > I had a similar problem last year when trying to use an index on a > char(8) field. Two solutions worked for me: 1) use "bpchar_ops", and > 2) leave out the operator class altogether. > Thanks, it worked. After reading your previous message, I guess I will omit the operator class altogether considering postgresql will most likely make a better decision than me anyways. Furthermore, since you seem to be quite familiar with this project, are you aware of any documentation for fine tuning postgresql? For instance, how can I make an educated decition whether to use char(32) or varchar(32)? From the documentation, under /docs/user/datatype960.htm, all four character types are shown in a table but there's no explanation as to which would be preferable in certain situations. If using explain is the way to go, it returned a higher cost for a sequential scan on a char() field compared to a varchar() field. Unfortunately, I suspect using char() does have some advantages I don't know about. Maybe these are newbie preoccupations, but I suspect there are a few of us out there. For the moment, the best tips I've received came from this mailing list and maybe this is the way to go. Thanks again for the help, Marc
Re: [GENERAL] Re: can't seem to use index
I have changed the name field to a char(32) NOT NULL, and I still get a sequential scan. I have added the 2500 records and I did "vacuumdb database" from the command-line. Unfortunately, "vacuum analyze" from the psql prompt returns a pqReadData() error, loses the connection to the backend and returns me to the shell. After reconnecting to the database, explain still returns a sequential scan when trying something like: explain select * from manufacturer where name='3COM'; Thanks anyways for the tip, I've been using varchar() all over the place, I think I'll change a few to char(). What are the advantages of using char() instead of varchar(). For a sequential scan, explain returned a cost of 105.44 for a char() field as opposed to 95.44 for a varchar(). Thanks again, Marc > --- admin <[EMAIL PROTECTED]> wrote: > > > I'm trying to use an index on a varchar(32) field, but explain > > keeps > > > retuning a sequential scan. This is my table and index: > > > > > > CREATE TABLE manufacturer ( > > > id int2, > > > name varchar(32) > > > ); > > > > > > CREATE INDEX manu_name_idx ON "manufacturer" using btree ( "name" > > > "text_ops" ); > > Do you really need a varchar ? I've got similar queries on a char > column which use the index. Maybe it's a problem about text_ops, > it may not be compatible with varchar. > > Alain > __ > Do You Yahoo!? > Talk to your friends online with Yahoo! Messenger. > http://im.yahoo.com >
Re: [GENERAL] Re: can't seem to use index
Yes, I did try vacuum analyze, but my search query still uses a sequential scan. > > I then tried changing the btree index to a hash talbe and went through the > > same procedure of vacumming and restarting a psql session. Yet again, the > > index wasn't being used. > > But did you try vacuum analyze or just vacuum? >
Re: [GENERAL] hash taboo?
Excellent point, your last comment gives me a tangible incentive for using hash instead of btree. Since I don't need to use other operators than '=', there is really no need to spend extra time creating a btree while all I need is a hash table. In the end, both are as fast for searching, but I gain some additional speed for inserting and removing entries. > > My results were exactly the same for btree and hash, even when vacumming > > between each index creation. Here's my query: > > SELECT * FROM prod_base WHERE mid='2'; > > > > Here's my result: > > Index Scan using prod_mid_idx on prod_base (cost=2.05 rows=2 width=120) > > > > My database is perhaps not big enough to run some relevant tests, so > > please let me know if there's another way I could get a better idea of the > > resources used for using each searching method. > > You have to look at index creation speed and index access speed. > > Not sure which one wins in each category. Also, index modification > speed may be important. Thanks again, Marc
Re: [GENERAL] hash taboo?
My results were exactly the same for btree and hash, even when vacumming between each index creation. Here's my query: SELECT * FROM prod_base WHERE mid='2'; Here's my result: Index Scan using prod_mid_idx on prod_base (cost=2.05 rows=2 width=120) My database is perhaps not big enough to run some relevant tests, so please let me know if there's another way I could get a better idea of the resources used for using each searching method. > Run some performace tests and let us know. > > > I've been reading the postgresql manual and I find there is very little > > discussion about hash compared to btree. Most of the focus seems to be on > > using btree indices even that the default for 'create index' is btree > > also. From the documentation, it seems the only difference between either > > searching method is that btree can be used with multiple operators whilst > > hash can only be used with '='. Furthermore, hash seems to be contained in > > memory, so should be limited to small queries or, in my case, queries > > using limit (without using sort which would need to retrieve the entire > > data anyways). > > > > My conclusion is that if I can live with just using '=' and using slightly > > more memory, I should be using hash. Unfortunately, there is very little > > sign in the documentation that I should be using hash at all. Perhaps I > > have missed something important. > > > > If someone could help me make a more rational decision on using searching > > methods, I'd appreciate. > > > > Thanks in advance, > > Marc > > > > > > > > > > > > > -- > Bruce Momjian| http://www.op.net/~candle > [EMAIL PROTECTED]| (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 >
[GENERAL] hash taboo?
I've been reading the postgresql manual and I find there is very little discussion about hash compared to btree. Most of the focus seems to be on using btree indices even that the default for 'create index' is btree also. From the documentation, it seems the only difference between either searching method is that btree can be used with multiple operators whilst hash can only be used with '='. Furthermore, hash seems to be contained in memory, so should be limited to small queries or, in my case, queries using limit (without using sort which would need to retrieve the entire data anyways). My conclusion is that if I can live with just using '=' and using slightly more memory, I should be using hash. Unfortunately, there is very little sign in the documentation that I should be using hash at all. Perhaps I have missed something important. If someone could help me make a more rational decision on using searching methods, I'd appreciate. Thanks in advance, Marc
[GENERAL] making 'like' queries quicker
Is there a way to make queries using the 'like' operator quicker, more specifically for queries that look like: select name from table where name like '%abc%'; Thanks, Marc
[GENERAL] indices don't make much difference
I am trying to optimise a query which looks like:
select prod_base.*, manu_base.name from prod_base, manu_base where
prod_base.mid=manu_base.mid;
manu_base is a table consisting of 3000 manufacturer with an id (not
unique to support synonyms) and a name (declared as varchar(32)).
prod_base is a table of products which each refer to the manufacturer id
(mid).
I have tried creating an index for manu_base using the following commands:
create index manu_mid_idx on "manu_base" using btree ("mid" "int2_ops");
drop index manu_mid_idx
create index manu_mid_idx on "manu_base" using hash ("mid" "int2_ops");
drop index manu_mid_idx
I have then run benchmarks without index, with btree and with hash, but
none seem to be faster than the other. My benchmark program is written in
c and is attached to this email. Here are the results I obtained using
time:
without index:
17.25 real 1.42 user 0.26 sys
with btree:
17.28 real 1.38 user 0.30 sys
with hash:
17.22 real 1.37 user 0.32 sys
If there is any way to make a query quicker when joining a product table
and a manufacturer table, please let me know. I've tried everything and
the results are quite fast enough.
Thanks,
Marc
#include
#include
#include
#include "libpq-fe.h"
static void exit_nicely(PGconn *conn) {
PQfinish(conn);
exit(1);
}
int main() {
int i, j;
char *dbName = "wtbwts";
PGconn *conn;
PGresult *res;
conn = PQsetdb(NULL, NULL, NULL, NULL, dbName);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
fprintf(stderr, "%s", PQerrorMessage(conn));
exit_nicely(conn);
}
for (i=0; i<50; i++) {
res = PQexec(conn, "SELECT prod_base.*, manu_base.name FROM prod_base,
manu_base where prod_base.mid = manu_base.mid");
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) {
fprintf(stderr, "SELECT failed\n");
PQclear(res);
exit_nicely(conn);
}
for (j=0; j
[GENERAL] update within limits
Quick question: Is there a way to update fields in a table within limits, similarly to using a select. In other words, something like this: update from table1 field1='blah' where id=(select id from table2 limit 5,5); Thanks, Marc
No Subject
No Subject
[GENERAL] dying backend processes
Hi all, we are using Postgresql 6.4.2 on FreeBSD 2.2.8 and have a lot of problems with suddenly dying backend processes. We have already changed kernel parameters to get more shared memory and start the postmaster with -B 1024. The postmaster's virtual memory limit is currently 300MByte and our largest tables contain about 12000 records. vacuum runs nightly and reports no errors. The problem arises mostly after a couple of INSERT or SELECT INTO statements, but also a 'COPY mytable FROM stdin' fails when I try to load a file with 13000 datasets. It works when I split it up into several files of not more than 5000 datasets each and load them separately. Turning on debugging for the postmaster as well as the backends is not very helpful for us because the server seems to behave differently. For Example, with debugging turned on we can't create a certain view, which works without the debugging options. Anyway, debug level 3 produced the following message: postmaster: reaping dead processes... postmaster: CleanupProc: pid 15608 exited with status 139 postmaster: CleanupProc: reinitializing shared memory and semaphores shmem_exit(0) [#0] binding ShmemCreate(key=52e389, size=8852184) Does anyone know what 'status 139' means or where we can find a hint in the source? Any further ideas how we can track down the cause of our dying backends? Thanks in advance Mirko
