|
|
|
|
|
|
| Author |
Message |
server
*nix forums addict
Joined: 19 Feb 2005
Posts: 60
|
 Posted: Tue Mar 01, 2005 8:25 am Post subject:
to_char bug?
|
|
|
|
message unavailable |
|
| Back to top |
|
 |
Ben Trewern
*nix forums beginner
Joined: 01 Mar 2005
Posts: 14
|
| Posted: Tue Mar 01, 2005 8:25 am Post subject:
Re: to_char bug?
|
|
|
From the docs:
"FM suppresses leading zeroes and trailing blanks that would otherwise be
added to make the output of a pattern be fixed-width"
It works now but for one I don't understand why the space is added in the
firs place and two I think the docs don't tell the whole story ie leading
blanks and I assume trailing zeros if applicable.
Regards,
Ben
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:794.1109627049@sss.pgh.pa.us...
| Quote: | "Ben Trewern" <bendottrewern@mowlem.com> writes:
It seems that to_char(1, '000') gives a string " 001" with a space in
front.
Is this a bug?
No.
Possibly you want 'FM000'.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
---------------------------(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 |
|
| Back to top |
|
|
Dinesh Pandey
*nix forums beginner
Joined: 18 Apr 2005
Posts: 36
|
| Posted: Tue Mar 01, 2005 8:25 am Post subject:
Re: [SQL] index row size 2728 exceeds btree maximum, 2713
|
|
|
Hi,
One of the columns in primary key is of type "TEXT". I am able to insert
with small data, but for around 3000 characters it's failing. How to handle
that?
Thanks
Dinesh Pandey
_____
From: Ramakrishnan Muralidharan
[mailto:ramakrishnanm@pervasive-postgres.com]
Sent: Thursday, June 02, 2005 3:11 PM
To: dpandey@secf.com; pgsql-general@postgresql.org; PostgreSQL
Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713
Hi,
The issue looks like your Index width exceeds the maximum width
of the index key limit, Please review the keys used in the index.
Regards,
R.Muralidharan
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey
Sent: Thursday, June 02, 2005 12:35 PM
To: pgsql-general@postgresql.org; 'PostgreSQL'
Subject: [SQL] index row size 2728 exceeds btree maximum, 2713
TABLE
-----------+-----------------------+-----------
Column | Type
-----------+-----------------------+-----------
scan_id | bigint
host_ip | character varying(15)
port_num | integer
plugin_id | integer
severity | character varying(50)
data | text
Indexes:
"pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num,
plugin_id, severity, data)
On inserting record I am getting this error "index row size 2728 exceeds
btree maximum, 2713"
How to solve this problem? |
|
| Back to top |
|
|
Richard Huxton
*nix forums Guru
Joined: 01 Mar 2005
Posts: 522
|
| Posted: Tue Mar 01, 2005 12:47 pm Post subject:
Re: Problem with pg_hba.conf
|
|
|
Sumit Rohatgi wrote:
| Quote: | In the pg_hba.conf file, I have the following entry:
host db1 client1 192.168.150.234/32 md5
Now the problem is:
The client having IP 192.168.150.234 is trying to
use phpPgAdmin, but is unable to connect.
The corresponding config.inc.php has this entry:
$conf['servers'][0]['desc'] = 'PostgreSQL';
$conf['servers'][0]['host'] = '192.168.150.234';
|
You're trying to connect from/to the same IP address. Might it be using
the loopack (127.0.0.1) instead?
Try adding a definition for 127.0.0.1 and see how that goes.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster |
|
| Back to top |
|
|
Hrishikesh Deshmukh
*nix forums beginner
Joined: 01 Mar 2005
Posts: 48
|
| Posted: Tue Mar 01, 2005 1:07 pm Post subject:
Re: GUI
|
|
|
Hi All,
I want to spend less time coding and more time running queries against
the DB which i am building. What about QT from TrollTech!! Can that be
used to whip up gui real fast! Dreamweaver?
Hrishi
On Tue, 01 Mar 2005 07:55:04 +0000, Richard Huxton <dev@archonet.com> wrote:
| Quote: | Hrishikesh Deshmukh wrote:
A GUI for the web running on linux.
Don't forget to CC: the mailing list too. I don't read this email
address very often - lots of spam.
Your problem isn't a lack of choices, but rather too many.
Perl has plenty of HTML templating systems from the simple
HTML::Template through to HTML::Mason - more of a website-building
system. For database access, DBI with DBD: g is the standard way to go.
CPAN is your friend here, of course.
Python has its own set of modules. Start looking here:
http://www.python.org/moin/WebProgramming
http://www.python.org/topics/web/HTML.html
You might want to look at Zope as an application framework too.
Ruby has many people who swear by it, and there's been a lot of fuss
recently over "Ruby on Rails" which is supposed to be a very fast
development setup
http://www.rubyonrails.org/
Java and its various servelet/jsp structures are too big a topic to
cover here.
In addition, there are a lot of content-management systems and toolkits
that might be useful for you. Two worth looking at are Plone and Bricolage.
HTH
--
Richard Huxton
Archonet Ltd
|
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) |
|
| Back to top |
|
|
Richard Huxton
*nix forums Guru
Joined: 01 Mar 2005
Posts: 522
|
| Posted: Tue Mar 01, 2005 1:24 pm Post subject:
Re: GUI
|
|
|
Hrishikesh Deshmukh wrote:
| Quote: | Hi All,
I want to spend less time coding and more time running queries against
the DB which i am building. What about QT from TrollTech!! Can that be
used to whip up gui real fast! Dreamweaver?
|
Neither of these are really application tools.
Give "Ruby on Rails" a look - that's probably about as fast as you're
going to get for simple tools.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org |
|
| Back to top |
|
|
Marco Colombo
*nix forums addict
Joined: 01 Mar 2005
Posts: 51
|
| Posted: Tue Mar 01, 2005 1:28 pm Post subject:
Re: Clay Shirky observation regarding MySQL
|
|
|
On Mon, 28 Feb 2005, Martijn van Oosterhout wrote:
| Quote: | On Mon, Feb 28, 2005 at 01:46:16PM -0600, bill@wadley.org wrote:
Hello!
Clay Shirky made a comment about MySQL that I thought the PostgreSQL
community should be aware of:
http://www.shirky.com/writings/situated_software.html
It's the section (mostly toward the bottom) entitled, "The Nature of
Programming, and the Curious Case of MySQL". The whole article is, as
normal, interesting and thought-provoking.
Interesting article, but w.r.t. to the MySQL statement, I read: If you
don't need any of the things that databases are good for (ACID,
transactions, triggers, views) then MySQL is an acceptable choice.
|
Interesting article, but I'm not much forgiving when a teacher
is so imprecise expecially on software history and qualities.
It is plain wrong that Apache made it easy, and that before apache
a web server was rocket science.
"[...] In the mid-90s, getting a web server running was such a messy
endeavor that it was a project goal in and of itself. Then Apache came
along, and so simplified the process that the web server became a simple
building block for larger things."
I've used both cernd and NCSA httpd, and when I finally switched
to Apache the choice was based on project activity: faster evolution,
more features, better stability, certainly not because it was easier.
Apache is quite a beast to configure from scratch even nowadays,
I'd even say it's _more_ complicated than it used to be (think of the
different server models it supports, and the large number of modules
available). Fore sure, running cernd was not any harder than running
apache is today. Mr. Shirky completely missed the point here.
"[...] MySQL makes the job much easier, so much easier in fact that
after MySQL, it becomes a different kind of job. There are complicated
technical arguments for and against using MySQL vs. other databases,
but none of those arguments matter anymore."
Same goes for MySQL. I don't think MySQL is easier to install,
configure and administer: the tasks are almost the same of other
open source databases. And _definitely_ it's not easier to use!
I'm not following their development much, but when I had to use it
I've _always_ found that missing features do make it harder to use MySQL.
Hell, even NATURAL JOIN is nice sugar for small projects, where you
can name columns and tables so that you write queries so "naturally",
hardly having to stop and think. So are foreign keys. These are
_not_ advanced features that only SQL gurus use: they reduce development
time and save headaches for _most_ application programmers. They
make programming faster, safer, easier.
So, MySQL success is _not_ based on user-friendlyness. Again, wrong
example.
Back in '95, on the Microsoft side, they didn't even know about TCP/IP.
Linux and i386/NetBSD were quite hard to get and install, not to mention
completely unknown to the public. No wonder running a web server was not
for everyone.
Today, we have distributions that come with a ready-to-run web server.
On the Microsoft side, they turned to Unix (NT/2000/2003 is POSIX, and
even Bill used to claim "NT is Unix"), and to Internet services. You
can run many Unix daemons and they have thier own Web server and SQL
server.
So, Mr. Shirky is right, installing and running a web server, or a RDBMS,
today is a matter of a few mouse clicks. But _not thanks to Apache
and MySQL_ (and to do that _professionally_ is still totally another
matter). They're only small bricks in the comfortable house build by the
open source movement as a whole.
Development teams behind projects such as Apache (which today is a lot
more than a HTTP server) and PostgreSQL pursue the goal of making
good products for _professionals_ to use effectively. Most of the burden
of making technologies available to as many non-guru users as possible
is on distribution makers. If Mr. Shirky wants to set a date, and
say "before that" and "after that", it's the day open source
distrubutions hit the masses. Certainly there's no "after Apache" and
no "after MySQL".
..TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster |
|
| Back to top |
|
|
Sean Davis
*nix forums Guru Wannabe
Joined: 01 Mar 2005
Posts: 172
|
| Posted: Tue Mar 01, 2005 1:33 pm Post subject:
Re: GUI
|
|
|
If you are into perl, you should definitely look at Class::DBI
(http://www.class-dbi.com) which is freely available from CPAN (despite
the .com site). It treats table rows as objects with methods. It can
model foreign key relationships based entirely on the database schema
(and entirely automatically). Then, there are simple extensions that
add methods for generating an HTML form directly from the database,
validate the resulting queries, etc. It can be a very fast way to
develop a web-based application.
Sean
On Mar 1, 2005, at 9:07 AM, Hrishikesh Deshmukh wrote:
| Quote: | Hi All,
I want to spend less time coding and more time running queries against
the DB which i am building. What about QT from TrollTech!! Can that be
used to whip up gui real fast! Dreamweaver?
Hrishi
On Tue, 01 Mar 2005 07:55:04 +0000, Richard Huxton <dev@archonet.com
wrote:
Hrishikesh Deshmukh wrote:
A GUI for the web running on linux.
Don't forget to CC: the mailing list too. I don't read this email
address very often - lots of spam.
Your problem isn't a lack of choices, but rather too many.
Perl has plenty of HTML templating systems from the simple
HTML::Template through to HTML::Mason - more of a website-building
system. For database access, DBI with DBD:g is the standard way to
go.
CPAN is your friend here, of course.
Python has its own set of modules. Start looking here:
http://www.python.org/moin/WebProgramming
http://www.python.org/topics/web/HTML.html
You might want to look at Zope as an application framework too.
Ruby has many people who swear by it, and there's been a lot of fuss
recently over "Ruby on Rails" which is supposed to be a very fast
development setup
http://www.rubyonrails.org/
Java and its various servelet/jsp structures are too big a topic to
cover here.
In addition, there are a lot of content-management systems and
toolkits
that might be useful for you. Two worth looking at are Plone and
Bricolage.
HTH
--
Richard Huxton
Archonet Ltd
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
|
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend |
|
| Back to top |
|
|
James Thompson
*nix forums beginner
Joined: 01 Mar 2005
Posts: 4
|
| Posted: Tue Mar 01, 2005 1:42 pm Post subject:
Re: GUI
|
|
|
On Tuesday 01 March 2005 08:07 am, Hrishikesh Deshmukh wrote:
| Quote: | Hi All,
I want to spend less time coding and more time running queries against
the DB which i am building. What about QT from TrollTech!! Can that be
used to whip up gui real fast! Dreamweaver?
Hrishi
|
You could use gnue-designer and gnue-forms from www.gnuenterprise.org. It
lets you paint the input/query forms by drag and drop fields from the db.
You can create multi table master/details forms from existing tables in
seconds that give you insert, update, delete, query capabilities. You can
assign triggers to forms to do custom processing. And if you need more
customization you can write python scripts using the gnue-common library
which is the core of our other tools. I've done that to create custom apps
that tie our database access system to a ui built with pyqt and qt-designer.
Several of the developers hang out in #gnuenterprise on irc.freenode.net if
you'd like more info.
Take Care,
James
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq |
|
| Back to top |
|
|
Vitaly Belman
*nix forums beginner
Joined: 01 Mar 2005
Posts: 6
|
| Posted: Tue Mar 01, 2005 2:48 pm Post subject:
Re: Backupping the table values
|
|
|
I tried looking into your solution.. However, the "DEFERRABLE
INITIALLY DEFERRED" doesn't seem to act as I expect it to. I made two
sample tables:
--------------------------------------------------------------------------------
CREATE TABLE functions.temp1
(
id1 int4 NOT NULL,
id2 int4,
CONSTRAINT pk_temp1 PRIMARY KEY (id1),
CONSTRAINT temp2_id2 FOREIGN KEY (id2) REFERENCES functions.temp2
(id2) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY
DEFERRED
)
WITHOUT OIDS;
CREATE TABLE functions.temp2
(
id2 int4 NOT NULL,
CONSTRAINT temp2_pk PRIMARY KEY (id2),
CONSTRAINT temp2_id2_key UNIQUE (id2)
)
WITHOUT OIDS;
--------------------------------------------------------------------------------
Then I tried to run the following SQL:
begin;
delete from temp2;
delete from temp1;
end;
Based on what the documentation says, this transaction should've
worked, but instead all I get is:
ERROR: update or delete on "temp2" violates foreign key constraint
"temp2_id2" on "temp1"
DETAIL: Key (id2)=(1) is still referenced from table "temp1".
Have I done anything wrong?
On Sat, 26 Feb 2005 19:56:32 -0600, George Essig <george.essig@gmail.com> wrote:
| Quote: | On Sat, 26 Feb 2005 16:47:38 +0200, Vitaly Belman <vitalyb@gmail.com> wrote:
So basically what I have is:
public schema, in which there are two tables, A and B.
backup schema, in which there are two tables, A and B.
On table A and B in public I add a trigger "On Delete" which inserts
the deleted data to the matching tables in the backup scehma.
That'd work fine except the foreign keys problem. In A I have a
column, "B_id" that is a foreign key to an "id" in the B table. Thus
it means that I have to delete from A before I delete from B.
Inserting into the backup folders, on the other hand, should be
reversed, from the same reasons.
Anyone has an idea how to solve this?
Maybe a DEFERRABLE INITIALLY DEFERRED foreign key would work. This
will cause the foreign key constraint to be checked at the end of the
transaction. See the manual at:
http://www.postgresql.org/docs/8.0/static/sql-createtable.html
George Essig
|
--
ICQ: 1912453
AIM: VitalyB1984
MSN: tmdagent@hotmail.com
Yahoo!: VitalyBe
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings |
|
| Back to top |
|
|
Wes
*nix forums addict
Joined: 01 Mar 2005
Posts: 57
|
| Posted: Tue Mar 01, 2005 3:17 pm Post subject:
Re: Vacuum time degrading
|
|
|
On 2/28/05 6:53 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
| Quote: | If you are suffering bloat, the fastest route to a solution would
probably be to CLUSTER your larger tables. Although VACUUM FULL
would work, it's likely to be very slow.
|
How can there be bloat if there are no deletes or modifies?
Even if there were deletes or modifies (there will be in about another year
and a half), if a vacuum is being performed every night, how can there be
bloat? The vacuum should release the dead space and it should be reused.
Am I missing something?
| Quote: | There are currently no deletes or modifies to the database - only inserts.
You *certain* about that? It's hard to see how the vacuum time wouldn't
be linear in table size if there's nothing to do and no dead space.
|
Absolutely sure. The only case that would approach a delete is if a batch
load fails, the transaction is rolled back. That very seldom happens.
Why am I running vacuum nightly if I have no deletes or updates, you ask?
Two reasons - to have it in the cron schedule for when there are deletes
(there will never be updates), and as a check on database integrity. If
there is a database problem, vacuum at least has a chance of flagging it
since it reads the entire database. This was instigated after we had a
couple of instances of corruption a while back that went undetected for too
long. I'm also doing a weekly pg_dumpall as an additional check/fallback.
| Quote: | Again, VACUUM VERBOSE info would be informative (it's sufficient to look
at your larger tables for this).
|
I'll set that up to run tonight and see if it gives any clues. Last night,
vacuum ran over 5 hours.
Wes
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org |
|
| Back to top |
|
|
Wes
*nix forums addict
Joined: 01 Mar 2005
Posts: 57
|
| Posted: Tue Mar 01, 2005 3:22 pm Post subject:
Re: Vacuum time degrading
|
|
|
On 2/28/05 6:53 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
| Quote: | It's hard to see how the vacuum time wouldn't
be linear in table size if there's nothing to do and no dead space.
|
I am doing 'vacuum analyze' rather than just 'vacuum'. Could that have
anything to do with the non-linear behavior?
Wes
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend |
|
| Back to top |
|
|
Karsten Hilbert
*nix forums addict
Joined: 01 Mar 2005
Posts: 91
|
| Posted: Tue Mar 01, 2005 3:41 pm Post subject:
Re: row numbering
|
|
|
| Quote: | There are 5 vaccinations in a given vaccination schedule.
Patient had 3 shots.
I want the view to show me that shot 4 and 5 are missing
without having to enter the cardinality of the vaccination in
the original data.
That sounds like you are trying to abuse the data model, so I'm not
surprised that it isn't easily possible. As the data stored in a table
is inherently unordered,
I know. I don't expect rows in tables to be ordered in any |
way.
| Quote: | you can't really talk about order unless you
impose it yourself by way of assigning ordinal numbers or some other
sort key to your rows.
Here is the bit of data that I forgot to mention: Those |
consecutive immunization rows *are* ordered by a sort key that
the application assigns -- the date of application. So,
basically, what I want to do is the following (high level):
1) read given vaccinations from table
2) order by date_given
3) assign ordinals to the rows in the order obtained by 2)
4) deduce missing shots by joining to another table that
defines the number of vaccinations in a schedule
Sure, I can do this in client code just fine. I would prefer
to put that into a view, however. Having "output row numbers"
doesn't make that *easy* but it seems it's the missing link to
making it *possible* in SQL. I am not asking for "row numbers"
for tables - which is nonsense - but rather for optional
numbering of query result rows.
| Quote: | Even if you could, say, assign a fixed order to tables or views or
actually had some kind of automatic row number available, that would
still make the semantics of your data dependent of the particularities
of the queries that you use to access it, which doesn't sound like a
good idea to me.
Understood. That wasn't what I was after. I poorly presented |
the case.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster |
|
| Back to top |
|
|
Karsten Hilbert
*nix forums addict
Joined: 01 Mar 2005
Posts: 91
|
| Posted: Tue Mar 01, 2005 3:43 pm Post subject:
Re: row numbering
|
|
|
| Quote: | There are 5 vaccinations in a given vaccination schedule.
Patient had 3 shots.
I want the view to show me that shot 4 and 5 are missing
without having to enter the cardinality of the vaccination in
the original data.
For this kind of task you usually want to use a left (or right) join.
I thought so. I tried to get it done that way several |
times. I asked on this list more than once, too.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq |
|
| Back to top |
|
|
Greg Stark
*nix forums Guru Wannabe
Joined: 01 Mar 2005
Posts: 155
|
| Posted: Tue Mar 01, 2005 3:59 pm Post subject:
Re: multicolumn GIST index question
|
|
|
| Quote: | Ron Mayer wrote:
Did anyone get multi-column GIST indexes working using both
the gist_btree and postgis modules?
|
Multi-column gist indexes are basically useless at this point. The index pages
are split based entirely on the first column, so the index becomes basically
an index on the first column.
There was some discussion on changing this but there wasn't consensus on which
direction to head with it. It may come up again for 8.1 if someone wants to
look at it.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Tue Dec 02, 2008 6:40 am | All times are GMT
|
|
Mobile Phones | Mortgages | Hotel Portal France, Italy, UK | Gas Suppliers | Loans
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
PostgreSQL
