tomyeh / postgresql Goto Github PK

Using this package in a flutter test project gives following error:

E/flutter (22587): [ERROR:topaz/lib/tonic/logging/dart_error.cc(16)] Unhandled exception:
E/flutter (22587): type 'Future<dynamic>' is not a subtype of type 'FutureOr<ConnectionImpl>' where
E/flutter (22587):   Future is from dart:async
E/flutter (22587):   FutureOr is from dart:async
E/flutter (22587):   ConnectionImpl is from package:postgresql2/src/postgresql_impl/postgresql_impl.dart

Switching flutter's SDK to disable dart 2 mode solves this issue but isn't really the solution long term.

Hi,

not connected by [scram-sha-256]

In the current implementation, it simply ignores errors including SocketException. Worse of all, the state is totally wrong that Dart VM has to restart even if the connection is back.

Goal

• Pass this kind of exception to the caller requesting a connection
• Make sure the pool can work correctly without restart, after the database is back

Hello,-
This is your insert query syntax in manual : -

    conn.execute('insert into crayons values (@id, @color)', {'id': 1, 'color': 'pink'})
         .then((_) { print('done.'); });

I understand that id & color are 2 columns in your DB .

This my PG DB :-

          create table data.usersData(
              dataIdDB serial ,
              dataCateg1DB varchar(30)  default ' ',
              dataCateg2DB varchar(30)  default ' ',
              dataEmailDB varchar(40) not null PRIMARY KEY default '',
              dataPassDB varchar(40) not null default ''
         );

I tried the insert query ,and I'm getting error when executing it ..though select query works fine :

        Future<String> registerNewUser(String email, String password) async {

        connectionPool = Persist()._getConnectionPool();

        Persist().poolMessages(connectionPool);

        await connectionPool.start().then((_) {

         connectionPool.connect().then((conn) {
        
         conn
            .query( 'select dataemaildb from data.usersData where dataemaildb=@emailValue
            order  by   dataIdDb',  {'emailValue': email}) .toList() 
            .then((selectResult) {

         // Select Query Works Fine and I get result

          if (selectResult.isEmpty == true && selectResult.length <= 0) {
            
            // I reach to hear ..all fine 
            
            conn.execute('insert into data.usersData values(@dataemaildb,@datapassdb)',
                [{'dataemaildb': email},{'datapassdb': password}])
               .then((insertResult) {
              
              // Here insert query fails and catchesError
              if (insertResult > 0) {
                registerString = 'reg';
              } else if (insertResult == null || insertResult <= 0) {
                registerString = 'nop';
              } else {
                registerString = 'nop';
              }
             
            }).catchError((insertError) {  //When insertQuery I catch Error !!
              registerString = 'nop';
              insertError.toString();
              
            }).whenComplete(() => Timer(Duration(minutes: 1), () {
                  conn.close();
                }));
          } else if (selectResult.isNotEmpty == true && selectResult.length > 0) {
            registerString = 'fal';
          
          } else if (selectResult == null) {
            registerString = 'nop';
            
          } else {
            registerString = 'nop';
           
          }
        })
        .catchError((selectError) {
          registerString = 'nop';
          selectError.toString();
         
        })
        .whenComplete(() => Timer(Duration(seconds: 10), () {
              conn.close();
            }));
  }).catchError((connectionError) {
    registerString = 'nop';
    connectionError.toString();
    
  }).whenComplete(() => null);
  
}).catchError((poolStartError) {
  registerString = 'nop';
  poolStartError.toString();
  
}).whenComplete(() => null);

  return registerNewUserString;
 }

From @finsignaresv on August 15, 2018 18:18

URL: https://pub.dartlang.org/packages/postgresql2#-installing-tab-

There is an error when somebody try to insert data in a table that has an autoincrement id:

I/flutter ( 1922): Query error: pgpool9:30264:9 ERROR 23505 duplicate key value violates unique constraint "table_pkey".

Is possible to insert the first row in the table, but if you try to upload a second one you will have this error, because the id do not increase automatically.

I hope you can fix this error soon.

Best regards,

Francisco.

Copied from original issue: dart-lang/pub-dev#1531

implement the methods startTransaction , commit , rollback like PHP PDO

https://www.php.net/manual/pt_BR/pdo.commit.php
https://www.php.net/manual/pt_BR/pdo.begintransaction.php
https://www.php.net/manual/pt_BR/pdo.rollback

bool beginTransaction() {
    connection.execute('begin');
    
  }

  bool commit() {
     connection.execute('commit');
  }


  bool rollback() {
     connection.execute('rollback');
  }

When server crashes, it takes a couple minutes to start. In this case, it is better to retry or wait a while. Thus, it is likely the caller (of connect) won't be affected, i.e., connect simply waits a bit longer than throwing back an exception immediately.

Hi!
This is a great package, thanks!
But what about transactions? Like in this package https://pub.dev/packages/postgres

Hello, sorry for my question.

I'm trying to get the return of the function below, which should return a List with the result of the select and return is null.

Can you help me please.

See function:

queryExec(String tipo, String sql, List param, List valparam) async {
List wret;
connect(uri).then((conn) {
switch (tipo.toUpperCase()) {
case 'SELECT':
{
conn
.query(sql, {param: valparam})
.toList()
.then((result) {
wret = result;
return wret;
})
.then((_) => conn.close())
.catchError((err) => print('Query ERRO: $err'));
break;
}
}
});
// return wret;
}

1. Rename freeConnections to limitConnection
2. Have 700ms wait controllable, and the duration is called limitTimeout

When receiving 57P01 and other severe errors, the pooled connection shall be removed from the pool.

In the current implementation, it just notices the caller and reuses the connection. It then causes connect to return a connection in bad state:

WARNING Connection returned in bad state. Removing from pool. state: closed transactionState: none. #pgpool1:49720:13

In a system with heavy load, it can allow all connections allowed (maxConnections). However, if many of these connections are short lived, we can reduce number of connections by waiting a while when the number of connections exceeds a certain number, say, freeConnections.

In other words, the connections are likely to release before we allocate a new one.

What if I want to make multiple insertions within single query? I should write something like this:

var sql = 'insert into my_table (id, name, create_date) values';
for (var obj in objects) {
  sql += "(${obj.id}, '${obj.name}', '${obj.createDate.toIso8601String()}'),";
}

But what if create_date field has type date (not timestamp)? In such cases I should write formatter in each insertion. It is annoying.

I wonder, what are you thinking about exporting Connection.substitute outside the library? Like this for instance:

var sql = 'insert into my_table (id, name, create_date) values';
for (var obj in objects) {
  sql +=  substitute(
    '(@id, @name, @create_date:date),',
    obj
  );
}

What status of Null safety Support?

I have a error:

dart run bin/server.dart 
Unhandled exception:
Unsupported or unknown authentication type: Unknown, only MD5 authentication is supported. (40004) #pg
#0      ConnectionImpl._readAuthenticationRequest (package:postgresql2/src/postgresql_impl/connection.dart:220:7)
#1      ConnectionImpl._readMessage (package:postgresql2/src/postgresql_impl/connection.dart:399:35)
#2      ConnectionImpl._readData (package:postgresql2/src/postgresql_impl/connection.dart:360:9)
#3      _RootZone.runUnaryGuarded (dart:async/zone.dart:1586:10)
#4      _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:339:11)
#5      _BufferingStreamSubscription._add (dart:async/stream_impl.dart:271:7)
#6      _SyncStreamControllerDispatch._sendData (dart:async/stream_controller.dart:774:19)
#7      _StreamController._add (dart:async/stream_controller.dart:648:7)
#8      _StreamController.add (dart:async/stream_controller.dart:596:5)
#9      _Socket._onData (dart:io-patch/socket_patch.dart:2324:41)
#10     _RootZone.runUnaryGuarded (dart:async/zone.dart:1586:10)
#11     _BufferingStreamSubscription._sendData (dart:async/stream_impl.dart:339:11)
#12     _BufferingStreamSubscription._add (dart:async/stream_impl.dart:271:7)
#13     _SyncStreamControllerDispatch._sendData (dart:async/stream_controller.dart:774:19)
#14     _StreamController._add (dart:async/stream_controller.dart:648:7)
#15     _StreamController.add (dart:async/stream_controller.dart:596:5)
#16     new _RawSocket.<anonymous closure> (dart:io-patch/socket_patch.dart:1849:33)
#17     _NativeSocket.issueReadEvent.issue (dart:io-patch/socket_patch.dart:1322:14)
#18     _microtaskLoop (dart:async/schedule_microtask.dart:40:21)
#19     _startMicrotaskLoop (dart:async/schedule_microtask.dart:49:5)
#20     _runPendingImmediateCallback (dart:isolate-patch/isolate_patch.dart:122:13)
#21     _RawReceivePortImpl._handleMessage `(dart:isolate-patch/isolate_patch.dart:193:5)

My code is:

  const defaultUri = 'postgres://postgres:[email protected]:5432/test_database';
  const uri = String.fromEnvironment("SPUN_BOOK_DB", defaultValue: defaultUri);
  var conn = await connect(uri);

Hello,
I'm getting a FormatException , this is the uri :

      var url = 'postgres://admin:SomePass!#[email protected]:5432/the_database';

The intelliJ AVD host address is 10.0.2.2 ;

changing the 'SomePass...' to 'somePass..' gave same exception

what could be the reason and solution ?

I/flutter ( 4076): connectionPool is inital
I/flutter ( 4076): pooledConnectionsCount is : 0
E/flutter ( 4076): [ERROR:flutter/lib/ui/ui_dart_state.cc(157)] Unhandled Exception: FormatException: Invalid port (at character 18)
postgres://admin:SomePass!#4@10.0.2.2:5432/the_database
E/flutter ( 4076): ^
E/flutter ( 4076):
E/flutter ( 4076): #0 new _Uri.notSimple. (dart:core/uri.dart:1484:11)
E/flutter ( 4076): #1 int._throwFormatException (dart:core-patch/integers_patch.dart:126:40)
E/flutter ( 4076): #2 int._parseRadix (dart:core-patch/integers_patch.dart:144:16)
E/flutter ( 4076): #3 int._parse (dart:core-patch/integers_patch.dart:102:12)
E/flutter ( 4076): #4 int.parse (dart:core-patch/integers_patch.dart:65:12)
E/flutter ( 4076): #5 new _Uri.notSimple (dart:core/uri.dart:1483:20)
E/flutter ( 4076): #6 Uri.parse (dart:core/uri.dart:1024:17)
E/flutter ( 4076): #7 new SettingsImpl.fromUri (package:postgresql2/src/postgresql_impl/settings.dart:30:17)
E/flutter ( 4076): #8 ConnectionImpl.connect (package:postgresql2/src/postgresql_impl/connection.dart:76:24)
E/flutter (

I'm using one as not SELECT but no return.

So: p.name = '% @ prod%'
Where prod would be a parameter.

Unsuccessfully.

Can you help me?

Is it possible to add a table field to Column class ? It could be useful in queries having joined-tables.

I have two tables (users and profiles) having some fields with the same name name as in the following table definitions:

CREATE TABLE users ( 
	id                   integer NOT NULL,
	name                 text NOT NULL,
	profile_id           integer NOT NULL,
	CONSTRAINT users_pkey PRIMARY KEY ( id )
 );
 CREATE TABLE profiles ( 
	id                   integer NOT NULL,
	name                 text  NOT NULL,
	CONSTRAINT profiles_pkey PRIMARY KEY ( id )
 );

In my query
List<Row> rows = await cnn.query("select users.*,profiles.* from users inner join profiles on users.profile_id=profiles.id").toList();

calling rows.first.getColumns() I can get a list of Column with two of these having the same name name because table-name alias was stripped. My software cannot decide which column-name name belongs to which table.

Moreover, rows.first.toMap() get me a map with a column-name name collapsed, loosing a column value.

I know, I could set an different alias to every column having the same name but this approach isn't easily
implemented by a generic software procedure.

Thanks,
Marco

https://www.postgresql.org/docs/9.1/arrays.html

I have a flutter app that connects directly to a postgresql database, most of the time there's no problem with the connection, but in some rare occasions I get this error:

The methods I use to interact with the database have this similar structure

Connection connection = await postgreSQLPool.connect();
try {
    List<Row> rows = await connection.query('select * from x').toList();
    return rows
        .map((e) => X(id: e[0]))
        .toList();
} catch (e) {
    throw e;
} finally {
    connection.close();
}

I can execute a single method 10+ times and it always works, I even tried putting the app on the background and locking the phone for 10 minutes, and when I open the app again and execute a db method, it works. But there still are some random occasions where this error appears.
The PgPool is being initialized in the main method:

void main() async {
  await postgreSQLPool.start();
  WidgetsFlutterBinding.ensureInitialized();
  runApp(
    MultiProvider(
      providers: [
        ChangeNotifierProvider(create: (_) => UserModel()),
        ChangeNotifierProvider(create: (_) => MainMenuModel()),
      ],
      child: App(),
    ),
  );
}

And postgreSQLPool is a global variable defined in a file like this:

final Pool postgreSQLPool = new Pool(
  'url',
  minConnections: 2,
  maxConnections: 2,
);

I even tried setting the timeout params for the Pool object to 1 minute thinking that maybe in those rare occasions the cause was a connection timeout, but after 2 minutes I still can connect to the database with no problems:

final Pool postgreSQLPool = new Pool(
  'url',
  minConnections: 2,
  maxConnections: 2,
  idleTimeout: Duration(minutes: 1),
  stopTimeout: Duration(minutes: 1),
  startTimeout: Duration(minutes: 1),
  establishTimeout: Duration(minutes: 1),
  connectionTimeout: Duration(minutes: 1),
  maxLifetime: Duration(minutes: 1),
);