Any reason why `mappedResultsQuery` was removed in V3?

It was useful when joining tables, when having same column name on different table.

List<Map<String, Map<String, dynamic>>> results = await connection.mappedResultsQuery(
  "SELECT t.id, t.name, u.name FROM t LEFT OUTER JOIN u ON t.id=u.t_id");

for (final row in results) {
  var tID = row["t"]["id"];
  var tName = row["t"]["name"];
  var uName = row["u"]["name"];
}

toColumnMap() override the value and cause issues when queries are like below.

Example if we have two table song and artist_song


song table
-------------
id   name 
-------------
1    Song A
2    Song B

artist_song table
---------------------------
id   song_id    artist_id 
---------------------------
8    1           1
9    2           1

Query : SELECT * FROM song LEFT JOIN artist_song ON song.id = artist_song.song_id

toColumnMap() will override the original id of song and as a result, it become 8.
I know I can do like SELECT song.* or use AS, but this is also not a good solution for my use case.

Postgres returns the table OIDs and you can access them in the result's schema.columns[i] object: https://pub.dev/documentation/postgres/latest/postgres/ResultSchemaColumn/tableOid.html

The v2 version was using an OID cache that mapped the ids to table names (if they were available), however, it added an extra query on the connection, somewhat randomly/unexpectedly, depending on the lifecycle status and use of the connection. It also did not scale well/produced bad results for a deployment that had the same schema names, but different creation histories, thus different OIDs, all behind a pgbouncer (so connection/query redirection happened transparently to the Dart connection object).

You can implement the same OID cache by querying the oid->name resolutions yourself, either in one go upfront or on demand as the app is running. If you think that it should be part of the package, I'm not opposed to providing it as a utility class, but probably not as part of the generic API. However, as the above use cases show, it is not just the simple use case we need to handle here.

Thanks for the quick response @isoos.

However I have fews question.

Is there any way that I can get table name from tableOid?
toColumnMap return UndecodedBytes in some of the field when data type is (CHAR).
When field value is UndecodedBytes, I try to use asString but returning with extra spaces in the values. Eg. instead of value active, I am getting active .
PS: I only checked with CHAR datatype, not sure if other data type have the same issue.

@necessarylion:

re 1: At the time of v3 release, the repository had the following old code for OID resolution:

postgresql-dart/lib/src/v2/connection.dart

Line 441 in 7a016f5

    
           "SELECT relname FROM pg_class WHERE relkind='r' AND oid IN ($unresolvedIDString) ORDER BY oid ASC",

In practice what you need is to execute the SELECT relname FROM pg_class WHERE relkind='r' AND oid=$1', [tableOid] query.

re 2: It looks like we don't support the CHAR type yet. We'll need to lookup its OID in postgres documentation and it should be an easy fix to the package afterward.

Assuming the last character is space and not a null terminator, the prior code may have called the equivalent of UndecodedBytes.asString.trim() on the return value.

@necessarylion: I've landed #274 to support the CHAR type. Could you please check if the current git HEAD works for you?

@isoos It is working, but still have extra spaces in the result with toColumnMap().

{
  uid: 1, 
  title: Awesome blog, 
  status: active  , 
  body: Awesome blog body, 
  slug: null, 
  deleted_at: null, 
  created_at: 2023-12-27 13:35:36.155773Z, 
  updated_at: 2023-12-27 13:35:36.155932Z
}

The spaces are there, because it is padded character type: the end will be added with spaces until the desired length has been reached, and the return value will contain the spaces. We could trim() it, but on the other hand, it may not what others would expect, and it is better to fall back on the value that postgresql actually has sent.

I'll do another quick double check and will do a release this evening.

@isoos

I just discovered the lack of support for the char type, I was testing the postgres v3 release in an existing project and came across this limitation, I'm glad you already implemented it, in relation to the extra space for char types in the previous version it was also like this , in PHP PDO it is also like this, I think that in most drivers it returns white space, which I think is correct. In relation to no longer executing the second query just to obtain information from the table, this is ideal, this improves performance

PHP

<?php
require 'vendor/autoload.php';

use Illuminate\Database\Capsule\Manager;
use Illuminate\Support\Arr;
use Illuminate\Support\Fluent;
use Illuminate\Container\Container;
use Illuminate\Support\Str;

$capsule = new Manager;
$capsule->setFetchMode(PDO::FETCH_ASSOC);
$capsule->addConnection([
    'driver' => 'pgsql',
    'host' => 'localhost',
    'port' => '5435',
    'database' => 'siamweb',
    'username' => 'user',
    'password' => 'pass',
    'charset' => 'win1252',
    'prefix' => '',
    'schema' => ['public'],
   
])
$capsule->setAsGlobal();
$db = $capsule->connection();
$res = $db->table('test_table')->limit(1)->get();
var_dump( $res);

dart

import 'package:eloquent/eloquent.dart';

void main(List<String> args) async {
  final manager = Manager();
  manager.addConnection({
    'driver': 'pgsql',
    'driver_implementation': 'postgres_v3',
    'host': 'localhost',
    'port': '5435',
    'database': 'siamweb',
    'username': 'user',
    'password': 'pass',
    'charset': 'win1252',
    'prefix': '',
    'schema': ['public'],

  });

  manager.setAsGlobal();

  final db = await manager.connection();
  final res = await db.table('test_table').limit(1).get();
  print('res $res');
}

3.0.5 published

Thank you @isoos