PostgreSQL update - datatype problemen

[rjvbeek]

Beste allemaal,

Na een update van onze database hebben wij een probleem met een hele oude applicatie die gebruik maakt van een PostgreSQL database. De database begint nu errors te geven op queries die eerst wel werkten: hij lijkt stricter te zijn op de datatypes (met errors als "No operator matches the given name and argument type(s). You might need to add explict type casts.").

Aangezien ik het systeem niet zelf heb gebouwd en niet alle queries langs kan om de correctheid ervan te checken, hebben we een workaround bedacht (I know....). We willen de pg_query() functie van PHP vervangen door een eigen implementatie waarbij uit alle queries de database velden en hun values worden gefilterd. Vervolgens kan ik via pg_field_type() de datatypes opzoeken en de values daaraan toetsen en waar nodig casten. Tenslotte moeten we de query opnieuw opbouwen en naar de database sturen.

Terug naar de oude database versie is helaas geen optie.

Twee vragen hierover:

1. Hoe ga ik die queries intepreteren? Ik ben niet handig met reguliere expressies...
2. Hoe ga ik die pg_query functie overschrijven in PHP? kan dat uberhaupt of moet ik hem echt anders noemen (en dus dearch/replace doen in de code, opzich geen ramp)

Alvast bedankt voor de tijd!

[RobIII]

rjvbeek schreef op woensdag 21 april 2010 @ 15:08:

Twee vragen hierover:

1. Hoe ga ik die queries intepreteren? Ik ben niet handig met reguliere expressies...
2. Hoe ga ik die pg_query functie overschrijven in PHP? kan dat uberhaupt of moet ik hem echt anders noemen (en dus dearch/replace doen in de code, opzich geen ramp)

2 antwoorden:
[list=1]
• Als je in een gesticht wil belanden wegens stress veroorzaakt door het klooien met reguliere expressies om dit werken te krijgen je teveel werd dan moet je vooral met reguliere expressies gaan werken
• Ik weet niet of je een functie kunt overloaden/overriden in PHP maar je zegt zelf al dat een search->replace van pg_query naar someother_query geen probleem moet zijn. En met 2 seconden googlen had je deze vraag zelf ook kunnen beantwoorden.

Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems. -- Jamie Zawinski

Dit is gedoemd te mislukken; dit ga je nooit (goed) werkend krijgen tenzij de queries misschien allemaal heel simpele selectjes zijn.

[ Voor 15% gewijzigd door RobIII op 21-04-2010 15:45 ]

[Kalentum]

Wat betreft punt 2: wat je kan doen is in de source code van de postgresql-extension van PHP de functienaam pg_query() wijzigen maar echt mooi is dat niet. Ik denk dat search & replace sneller en eenvoudiger is.

En punt 1: je zou op zoek moeten gaan naar een SQL parser, bv voor perl. Als de queries eenvoudig genoeg zijn dan zou dat moeten werken.

[cariolive23]

Welke versie van PostgreSQL gebruikte je eerder en welke versie gebruik je nu? En welke exacte foutmeldingen krijg je op welke queries?

Voordat je met regexen gaat klooien, moet je eerst even kijken wat nu het probleem is en welke mogelijke oplossingen er zijn. Je zou namelijk ook een nieuwe operator in PostgreSQL kunnen aanmaken, dat kan jouw probleem ook oplossen.

Edit:
In versie 8.2 werkt dit:

SQL:

SELECT '1'::varchar = 1;

In versie 8.4 krijg je een foutmelding:

ERROR: operator does not exist: character varying = integer

Het staat mij bij dat dit sinds versie 8.3 het geval is, maar die heb ik even niet bij de hand om te testen.

[ Voor 29% gewijzigd door cariolive23 op 21-04-2010 16:23 ]

[Zoijar]

Volgens mij kan je de pg_cast tabel aanpassen voor je specifieke database om impliciete casts toe te voegen. Hier staat ook zoiets.

http://petereisentraut.bl...-casts-in-postgresql.html

i.e. dit soort dingen:

SQL:

CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;

Als je uit je foutmeldingen kan achterhalen welke types hij niet naar wat kan casten, dan kan je zo een implicit cast toevoegen voor die conversie, en dan zou het moeten werken... mooie is dat je niks aan de code hoeft te veranderen, maar het gewoon op database niveau oplost.

[ Voor 66% gewijzigd door Zoijar op 21-04-2010 16:18 ]

[cariolive23]

Inderdaad, dat was de link die ik al zocht en even niet kon vinden...

[eghie]

doe een search/replace op pg_query en maak een wrapper functie van, als hij nog geen wrapper functie heeft. Daarmee kun je loggen welke queries er fout gaan.

Voorbeeld:

PHP:

function mypg_query($query) {
    $result = null;
    try {
        $result = pg_query($query);
        if (!$result)
            throw new Exception('error');
    } catch (Exception $e) {
        file_put_contents("/tmp/queries.log", sprintf("%s -- error: %s (Exception: %s)\n", $query, pg_last_error(), $e->getMessage()));
    }
    return $result;
}

Dan komt er in /tmp/queries.log de queries te staan die verkeerd gaan.

[rjvbeek]

Bedankt voor de reacties allemaal, ik heb het laatste scriptje iets omgebouwd zodat ik actief op de hoogte wordt gehouden van eventuele fouten. En debuggen maar!