[SQL] un-triviality is saved

[Dmitry Turin]

>What does this do that inheritance doesn't already do? I don't think I see 
>anything.

- So, we have proved, that all, what you offer to make on your a-a-a...
  Fortran, is possible to make on assembler.
- First, I know this without you, second, speech was not about that.

Newness is not "what", newness is "how" !

As to processing data,
new way allow to think in natural terms of tree, instead in artificial
terms of Decart's production and PL-extension for SQL.

As to communication,
new way allow to extract XML from database directly, without any
gasket (php, etc) - that is VERY important to untrained, inexperienced user.
Direct way open gates (doors) for applied specialists !

>> We speak about database, thus what user wants to do is
>> predetermined partly - there are at least two things: database and
>> visualizer (browser is only one case).

>This sounds to me entirely like a user-interface problem.

Situation is system.
And as any item, it has system's characteristics.
What's about difficulty:
difficulty-of-request + difficulty-of-transportation = invariant.

 If you make request without transport's possibility,
 then you must to enter one more language!
 It's very, very bad for un-experienced user.

And the main: un-triviality (un-banality) is saved !!!
you are forced to get this un-triviality in other language.

 But in addition to un-triviality, you have place of dock
 between SQL and other language.

Representation of data as tree (instead of as matrix) is not aim itself,
it's sideline-result.

---

>We don't need to make SQL more complicated (and less relational)

There are two question: "more complicated" and "less relational".

First:
SQL doesn't loss and doesn't gain any functional characteristics.
SQL does not become more complicated.

Second:
SQL doesn't loss and doesn't gain any relational characteristics, they are 
saved.
SQL does not become less relational.

>point of SQL was to move away from the hierarchical model

"Was", pay attention.
It doesn't mean, that it should be always.
Let's distinguis between two characteristics: harmness and orthodoxality.
Orthodoxality is not argument, i think.

AS> inexperienced user are willing to learn

What about user want to learn, user think:
what is the least, so is the best.
Minimum quantity of tools is the best.
This is fact instead of opinion.



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] ERROR: column "crc" does not exist

[ezequias]

Hi list,

I have a table with many fields and the latest field is:

"CRC" character varying(255),

When I try to access the table with a select with a where clause like

Select * from base.points where crc = 'e19e26330a0db2f2435106b16623fa82'

The error on the Subject of this e-mail allways appears.

Any help would be great.

Regards,
--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] ERROR: column "crc" does not exist

[George Weaver]

Original Message From: <[EMAIL PROTECTED]>


I have a table with many fields and the latest field is:

"CRC" character varying(255),

  ^^


Select * from base.points where crc = 'e19e26330a0db2f2435106b16623fa82'


What happens when you enter:

Select * from base.points where "CRC" = 'e19e26330a0db2f2435106b16623fa82';

Regards,
George



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] ERROR: column "crc" does not exist

[A. Kretschmer]

am  Thu, dem 12.04.2007, um  9:23:39 -0300 mailte [EMAIL PROTECTED] folgendes:
> Hi list,
> 
> I have a table with many fields and the latest field is:
> 
> "CRC" character varying(255),

You have created a field called "CRC", with upper-case.



> 
> When I try to access the table with a select with a where clause like
> 
> Select * from base.points where crc = 'e19e26330a0db2f2435106b16623fa82'

You searched for a field "crc", with lower-case. rewrite your query to :
... where "CRC" = 'e19e26330a0db2f2435106b16623fa82'


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] ERROR: column "crc" does not exist

[Andrew Sullivan]

On Thu, Apr 12, 2007 at 09:23:39AM -0300, [EMAIL PROTECTED] wrote:
> 
> "CRC" character varying(255),
  ^

> Select * from base.points where crc = 'e19e26330a0db2f2435106b16623fa82'
  ^^^

"CRC" != crc.  You need to spell the column name correctly.  Or don't
quote the identifier when you create the table.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] ERROR: column "crc" does not exist

[Bart Degryse]

Try either
- defining the field as: crc character varying(255)
- rewrite your select as: Select * from base.points where "CRC" = 
'e19e26330a0db2f2435106b16623fa82';

>>> <[EMAIL PROTECTED]> 2007-04-12 14:23 >>>
Hi list,

I have a table with many fields and the latest field is:

"CRC" character varying(255),

When I try to access the table with a select with a where clause like

Select * from base.points where crc = 'e19e26330a0db2f2435106b16623fa82'

The error on the Subject of this e-mail allways appears.

Any help would be great.

Regards,
--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com ( http://ezequiasrocha.blogspot.com/ )


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[SQL] replace EXCEPT by JOIN

[T E Schmitz]

The following construct does what I need but is there a better way of 
expressing this?




select * from
(

SELECT DISTINCT
 section.section_pk
FROM ITEM
 LEFT JOIN product product ON Item.product_fk = product.product_pk
 LEFT JOIN section section ON product.section_fk = section.section_pk
WHERE item.export = 'f'

EXCEPT

SELECT DISTINCT
 section.section_pk
FROM ITEM
 LEFT JOIN product product ON Item.product_fk = product.product_pk
 LEFT JOIN section section ON product.section_fk = section.section_pk
WHERE item.export = 't'

) as X

---(end of broadcast)---
TIP 1: 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: [SQL] Replace string

[PostgreSQL Admin]

test=*# select regexp_replace(regexp_replace('The blue fox\'s fur.', ' ', '-', 
'g'), '\\.', '');
   regexp_replace

 The-blue-fox's-fur
(1 row)


Andreas
  

Thanks for the input.


I came up with this:

REPLACE(regexp_replace('The blue fox\'s fur', '[[:punct:]]', ''), ' ', '-');

---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] question on plpgsql block

[Karthikeyan Sundaram]

Hi Gurus,
 
I tried a plpgsql block from the php.
 
example 
 
a='begin
  insert into table a values ();
  insert into table b values ()
 insert into table c values (...)
 select into p_var ... from table where condtion
 update a set column =  where condition;
commit;
end;';

I works fine when I call this block from php, whereas, I tried to copy the 
same value from the command line prompt or PGadmin GUI utility.  Then it says 
"ERROR:  current transaction is aborted, commands ignored until end of 
transaction block" 
 
  Why?
 
   Did we need to do any special step up to execute a block or blocks won't 
execue from command line mode? Please advise.
 
Regards
skarthi
 
 
 
_
Your friends are close to you. Keep them that way.
http://spaces.live.com/signup.aspx

[SQL] setting up a mirroring or replication database

[Karthikeyan Sundaram]

Hi Team,
 
We are using Postgres 8.1.0 and in the plans to migrate to 8.2.3.   Ours is 
a OLTP application.  Publishers, advertisers and consumers use our system world 
wide.  Right now it's not a very big database. But we are expanding our 
operations to Europe and US where we are expecting a moster growth.
 
I want to setup a mirroring database or replications database so that if 
one database crash, the load balancing database should take care or it.  Not 
only that, if I am upgrading one database, the other should take the load. 
 
I have never set this kind of replication or mirroring the database before. 
 What are the steps and procedures to do this kind of setup.
 
   Please advise.
 
Regards
skarthi
 
_
Live Search Maps – find all the local information you need, right when you need 
it.
http://maps.live.com/?icid=wlmtag2&FORM=MGAC01

Re: [SQL] setting up a mirroring or replication database

[Phillip Smith]

This may best be asked in the admin group - please don't cross-post. Let's
keep all further discussion in the admin group.

 

A good starting place for you would be the PostgreSQL manual, specifically
the chapter called "High Availability":

http://www.postgresql.org/docs/current/interactive/high-availability.html

. also Pgpool:

http://pgfoundry.org/projects/pgpool/

. and Slony:

http://slony.info/

 

Cheers,

~p

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Karthikeyan Sundaram
Sent: Friday, 13 April 2007 10:39
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: [SQL] setting up a mirroring or replication database

 

Hi Team,
 
We are using Postgres 8.1.0 and in the plans to migrate to 8.2.3.   Ours
is a OLTP application.  Publishers, advertisers and consumers use our system
world wide.  Right now it's not a very big database. But we are expanding
our operations to Europe and US where we are expecting a moster growth.
 
I want to setup a mirroring database or replications database so that if
one database crash, the load balancing database should take care or it.  Not
only that, if I am upgrading one database, the other should take the load. 
 
I have never set this kind of replication or mirroring the database
before.  What are the steps and procedures to do this kind of setup.
 
   Please advise.
 
Regards
skarthi

 

  _  

Live Search Maps - find all the local information you need, right when you
need it. Find   it!



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [SQL] A long-running transaction

[John Summerfield]

Andrew Sullivan wrote:

On Wed, Apr 11, 2007 at 10:23:34PM +0800, John Summerfield wrote:


Each row's identified by a key, and the key doesn't change.



That doesn't matter.



ADABAS would put the updated record right back where it came from, it



That isn't how PostgreSQL works.

I'm having a hard time laying my hands on the bits of the docs that
explain this (anyone?), but MVCC stands fo Multi-Version Concurrency
Control.  When you write a row, it has a version number.  That
version number tells other transactions what version of the row they
look at.

Here's a _really over simplified_ explanation, at a conceptual level.
[Note to experts: please don't correct details of how this is wrong.
I know that.  But John's problem is that he doesn't have this
conceptually yet.  The following explanation has worked for me in the
past in getting the idea across first, so that the details can later
make sense.]

Suppose you have two transactions, t1 and t2.  They start at
approximately the same time.  I'm assuming they're READ COMMITTED.

Suppose t1 updates row R.  That version of R has a number R(t1).

Suppose at the same time, t2 is looking at R.  The _version_ of R
that t2 sees is R(tn), where n<1.  This way, t2 does not have to
wait on t1 in order for t2 to proceed (unless t2 actually modifies R.
Ignore that for the sake of this explanation, please).

The way this works is that, in marking R(t1), the system says "R(t1) is
valid for transactions that committed after t1".  Since t2 hasn't
committed, it sees the old version.  So when t1 commits, there are
_two_ versions of R -- R(t1) and R(tn),n<1.  When all transactions
have committed such that there is no (tn),n<1, then the row is marked
dead, and can be recovered by VACUUM (this is one of the main things
VACUUM does.  The idea is to move the housekeeping of concurrency
control out of the main transaction.  Oracle does something
conceptually similar, except using rollback segments, which is why
long-running transactions can exhaust the supply of rollback segments
on Oracle).

Now, you're thinking, "But this is all in one transaction!"  Yes, but
the implementation details make it impossible that t1 rewrite the
same row over and over again, so your rewriting of the same row again
and again actually is creating huge numbers of dead rows.  The
unfortunate thing is that you have to follow the whole dead-row chain
to find the currently live version of your row.


Why is this implementation preferable to not doing that?



The upshot of this is that updating the same row over and over in one
transaction will make your transaction go slower on each round, until
you are nearly stopped.  That's why a lot of updates of the sort you
describe are in fact the worst case under Postgres.  EnterpriseDB


Where is enterprisedb? enterprisedb.com seems to be held by a squatter
in Kingston. About where they're playing an important cricket
competition about now.


seems to have a proposal (which I believe is going to make 8.3) that
will mitigate but not completely solve some of this.


I can see we need before and after, but why all those intermediate rows?
How can they possibly be used? If I roll back the transaction, they're
all discarded, and if I commit then only the last is required.

Also, I don't see why (apparently) a sequential search is used; surely
if all of these rows might be required, still a pointer to the last
would be right? Or last two, if you need the ability to check the order.

Is there a development version I can try, with this improvement in it?





user  :   1d  3:17:04.03  16.0%  page in :  131097310  disk 1:
3079516r20087664w
nice  :   0:05:39.64   0.1%  page out:  197016649
system:   2d 20:38:37.13  40.1%  page act:   87906251

That line above. The way I figure it the kernel's doing an enormous
amount of work handling its buffers.



No, I think it's doing an enormous amount of I/O, because it has to
keep looking for these new rows (and scanning over the old areas
while it's at it).  This is system time, if I recall correctly
through the haze that is now my understanding of Linux I/O, because
it's down at the I/O subsystem and can't actually be done by the user
program.  (Feel free to correct me on this.  But I still bet it's
dead rows.)


Your argument is fairly convicing, and it does reflect that the
postmaster seems always to be doing an update, but I am still dubious.
1. For the first day or so, my observation was that the disk was not
particularly busy. It seemed to be running about half the time. It is
fairly noticible because, for reasons I don't understand at all, it (on
the laptop) generates EMI and upsets the radio in the next room.
Unplugging the power cord stops the EMI - go figure, but of course I
only have a few hours before the battery runs out.

2. System time, unless I am mistaken (and that is possible), is time the
CPU is active in the kernel. It is separate from IOWait.


I'm reposting the whole lot:
Bootup: Tue Mar 27 18:50:19 2

Re: [SQL] question on plpgsql block

[A. Kretschmer]

am  Thu, dem 12.04.2007, um 15:44:19 -0700 mailte Karthikeyan Sundaram 
folgendes:
> Hi Gurus,
>  
> I tried a plpgsql block from the php.
>  
> example
>  
> a='begin
>   insert into table a values ();
>   insert into table b values ()
>  insert into table c values (...)
>  select into p_var ... from table where condtion
>  update a set column =  where condition;
> commit;
> end;';
>
> I works fine when I call this block from php, whereas, I tried to copy the
> same value from the command line prompt or PGadmin GUI utility.  Then it says
> "ERROR:  current transaction is aborted, commands ignored until end of
> transaction block"
>  
>   Why?

My guess:

PHP treat each line separately and sends line by line to the server and
adds the missing ';' if necessary.

If you send this from the GUI the server received one big command with
syntax-errors, because the missing ';' after some commands.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate